I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel © users who managed to understand its functionality.

But first the good part of SUMPRODUCT:

You do not need Excel's © worksheet functions COUNTIF or SUMIF. Actually, you may want to decide never to use them because you might need to increase the number of conditions - and they only support ONE (condition). [Ok, you could aggregate all conditions in a helper column and refer to that but let's drop that for now.]

But what should you use instead of COUNTIF if you want to count conditionally?

SUMPRODUCT!

Example: If you want to count all cells in A1:A100 which show a positive numerical value, e.g. which are greater zero:
=COUNTIF(A1:A100,>0)
can be replaced by
=SUMPRODUCT(--(A1:A100>0))

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
=SUMPRODUCT(--(A1:A100>0),--(B1:B100=LONDON))

What should you use instead of SUMIF if you want to sum up conditionally?

Again: 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:
=SUMIF(A1:A100,>0,B1:B100)
can be replaced by
=SUMPRODUCT(--(A1:A100>0),B1:B100)

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).

Example:
[AND Condition] If you want to add all numbers in C1:C100 where A1:A100 are negative and where B1:B100 show a YES:
=SUMPRODUCT(--(A1:A100<0),--(B1:B100=YES),C1:C100)

[OR Condition] If you want to add all numbers in C1:C100 where A1:A100 are negative or where B1:B100 show a YES:
=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100=YES)),C1:C100)
[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.]

You get enthusiastic about SUMPRODUCT and you tell everybody that this is the function they should use - again and over again? Wait a second:

Now the bad part of SUMPRODUCT:

But there are limits to its usage. SUMPRODUCT offers you a nice and complex functionality of an array function. You can count and sum up attributes/properties of cell ranges and put the result into a cell. If you need to do this for all appearing characteristics you face some difficulties, though. You need to prepare the set of unique characteristics for use with SUMPRODUCT first. If you do this manually you end up with manual maintenance in case your input changes. If you want to do this with worksheet functions you increase the complexity of your worksheet.

Example: If you need to count all different strings in a column and to list them together with their number of occurrences, for example, you have two reasonable options for a worksheet function approach with SUMPRODUCT:

a) To manually write down all different entries and to count these with COUNTIF or SUMPRODUCT. You would have to maintain this list manually then.
b) A feed of unique values with worksheet functions increases the complexity of the worksheet (please see here).

For cases like this one I suggest to use my user-defined-function (UDF) Pfreq instead of SUMPRODUCT/COUNTIF, if you need to count. If you want to sum up specified cells for all occurring corresponding cells/combinations, use Sfreq, please.