"Whenever you find yourself on the side of the majority, it is time to pause and reflect." [Mark Twain]
SUMPRODUCT is a very powerful function. You can conditionally count or sum large ranges of data. On the other hand, if you need to maintain statistics on ALL elements of a larger list, the manual maintenance effort and sometimes the runtime of a recalculation can be too high.
Advantages of using SUMPRODUCT:
SUMPRODUCT is more flexible than COUNTIF or SUMIF which support ONE condition only.
Example: If you want to count all cells in A1:A100 which show a positive numerical value, e.g. which are greater zero:
can be replaced by
And if you need to count how many numbers of A1:A100 are greater than zero AND where the string in column B is "LONDON", use
This cannot be done with COUNTIF.
Instead of using SUMIF if you can sum up conditionally with SUMPRODUCT.
Example: If you want to sum up all cells in B1:B100 where the corresponding cells in A1:A100 show a positive numerical value:
can be replaced by
Please note that the double unary minus translates the Boolean values True and False into the numbers 1 and 0. You could also have used 0+ or 1* to do this but I think the double unary minus canonically indicates a condition and if that's missing you know that you look at the values which you need to sum up.
Now the fun starts because you can combine conditions which COUNTIF and SUMIF cannot deal with (since there are more than one of them).
[AND Condition] If you want to add all numbers in C1:C100 where A1:A100 are negative and where B1:B100 show a "YES":
A more complex example which you still might find not too complex: Calculate the rental cost for a holiday property - see this 12 KB Excel 2007 sample file but open and use at your own risk, please read my disclaimer.
[OR Condition] If you want to add all numbers in C1:C100 where A1:A100 are negative or where B1:B100 show a "YES":
[Please notice that the SIGN function reduces possible overlapping OR criteria - which would sum up to more than 1 - to 1 exactly! I suggest to always wrap OR criteria checks into a SIGN() function. A simple sum is naive and outright dangerous because a less skilled person could amend non-overlapping XOR criteria to overlapping OR ones.]
More complex examples: