Difference between Sumifs and Countifs in a chart

**Befuddled** · 05-18-2010, 09:51 PM

I have 2 formulas, 1 uses Sumifs and the other 1 uses Countifs.
Is there a reason why the answer for the one with the Sumifs in it changes when I sort the data in the table, that those formulas use for data, yet the one that uses Countifs does not change?

**Befuddled** · 05-18-2010, 09:57 PM

Here is an example workbook.
The cells that change are AM5:BQ5
The cells that stay the same are AM3:BQ3
The data is sorted in Column E, if you "Select All", the answers in AM5:BQ5 change

**teylyn** · 05-18-2010, 10:13 PM

Sumifs creates a sum of the numbers that meet the specified condition.

Countifs counts how many cells meet the specified condition.

If you have 20 number and 10 of them meet the specified condition, Countifs will return a 10. Sumifs will return the result of the sum of those 10 numbers. If the number values change, the Countifs still counts 10 valid numbers, but the Sumifs will come back with the current sum result.

**Befuddled** · 05-18-2010, 10:23 PM

is there a way to write that formula, so that it doesnt change when you sort the table?

**teylyn** · 05-18-2010, 10:37 PM

Sorting and filtering the table does not have any effect on the results of Countifs or Sumifs, because they reference the whole table.

If you feel some of your results are wrong, inspect the formula with the Evaluate Formula tool to see what's going on.