SUMIF, COUNTIF

SUMIF(range, criteria, [sum_range])

1 2 3 4 5 6 7 8 9 10

A B C Property Value Commission Data 100,000 7,000 250,000 200,000 14,000 300,000 21,000 400,000 28,000 Formula Description Result =SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over 160,000. 63,000 =SUMIF(A2:A5,">160000") Sum of the property values over 160,000. 900,000 =SUMIF(A2:A5,300000,B2:B5) Sum of the commissions for property values equal to 300,000. 21,000 =SUMIF(A2:A5,">" & C2,B2:B5) Sum of the commissions for property values greater than the value in C2. 49,000

 

 

COUNTIF(range, criteria)

1 2 3 4 5 6 7 8 9 10 11 12

A B C Data Data apples 32 oranges 54 peaches 75 apples 86 Formula Description Result =COUNTIF(A2:A5,"apples") Number of cells with apples in cells A2 through A5. 2 =COUNTIF(A2:A5,A4) Number of cells with peaches in cells A2 through A5. 1 =COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2) Number of cells with oranges and apples in cells A2 through A5. 3 =COUNTIF(B2:B5,">55") Number of cells with a value greater than 55 in cells B2 through B5. 2 =COUNTIF(B2:B5,"<>"&B4) Number of cells with a value not equal to 75 in cells B2 through B5. 3 =COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85") Number of cells with a value greater than or equal to 32 and less than or equal to 85 in cells B2 through B5. 3