From the Not Just Numbers blog:
I have covered both SUMIFS and COUNTIFS in earlier posts.
To recap:
You can use COUNTIFS to count the instances of a value in a range, e.g.
=COUNTIFS(A1:A100,”H”)
will count how many cells with value “H” there are within the range A1:A100.
You can also add additional pairs of criteria, so that
=COUNTIFS(A1:A100,”H”,B1:B100,3)
will count how many cells with value “H” there are within the range A1:A100, where the corresponding value in the range B1:B100 is 3.
SUMIFS works in a similar way, but you enter an additional range as the first argument that is summed, e.g.
=SUMIFS(C1:C100,A1:A100,”H”,B1:B100,3)
will sum the entries in column C, where the corresponding entry in column A is “H” and B is 3.
Where these are both very useful functions, one particular flaw is that they are not case-sensitive.
So, there is no difference between
=COUNTIFS(A1:A100,”H”)
and
=COUNTIFS(A1:A100,”h”)
A client recently faced this problem in reporting from a holiday spreadsheet where a full day’s holiday is marked with an “H” and a half-day is marked with a “h”.
The client wished to total the number of days holiday, which if COUNTIFS was case-sensitive, would be as simple as:
=COUNTIFS(A1:A100,”H”)+(0.5*COUNTIFS(A1:A100,”h”))
But unfortunately, we can’t use COUNTIFS at all!
We do have a function in Excel that allows us to compare two values, taking account of case, and that is the EXACT function.
=EXACT(A1,B1)
will return TRUE if A1 is exactly the same as B1, so
=EXACT(“H”,”h”)
would return FALSE.
Unfortunately, we can’t use this with COUNTIFS, because COUNTIFS doesn’t allow us to simply insert a condition, as it requires us to enter the conditions in pairs, as above.
We can, however, use the SUMPRODUCT function, which works with arrays, to apply the EXACT function to a whole array (e.g. A1:A100).
=SUMPRODUCT(EXACT(A1:A100,”H”)*1)
The array EXACT(A1:A100,”H”) will be 100 TRUEs and FALSEs. By multiplying these by 1, they become 1s and 0s. So, this will now calculate the number of capital Hs for us.
To complete our example, we can add this to:
=SUMPRODUCT(EXACT(A1:A100,”h”)*0.5)
Giving us:
=SUMPRODUCT(EXACT(A1:A100,”H”)*1)+SUMPRODUCT(EXACT(A1:A100,”h”)*0.5)
which will return the number of Hs added to half the number of hs, giving us our total of holiday taken.
We can easily add multiple criteria too, as well as change it to the equivalent of SUMIFS.
So,
=COUNTIFS(A1:A100,”H”,B1:B100,3)
can be made case-sensitive by using
=SUMPRODUCT(EXACT(A1:A100,”H”)*1,(B1:B100=3)*1)
as SUMPRODUCT will multiply the resulting arrays of 1s and 0s, so the resulting array will only show a 1 when both conditions are true.
=SUMIFS(C1:C100,A1:A100,”H”,B1:B100,3)
can be made case-sensitive by using
=SUMPRODUCT(EXACT(A1:A100,”H”)*1,(B1:B100=3)*1,C1:C100)
as the we have added the array C1:C100, being the values in column C, which are multiplied by the 1s and 0s
So, say, cell A2 contains “H”, B2 contains 5 and C2 contains 30.
The second position in each of the arrays are as follows:
First array: A2 does exactly equal H so the result is 1
Second Array: B2 doesn’t equal three so the result is 0
Third Array: C2 is 30, so the result is 30
The SUMPRODUCT function multiplies these together so that the 2nd position in the resulting array is:
1 x 0 x 30 = 0
And, say, cell A3 contains “H”, B3 contains 3 and C3 contains 15.
The third position in each of the arrays are as follows:
First array: A3 does exactly equal H so the result is 1
Second Array: B3 does equal three so the result is 1
Third Array: C3 is 15, so the result is 15
The SUMPRODUCT function multiplies these together so that the 3rd position in the resulting array is:
1 x 1 x 15 = 15
Hopefully you can see that if both conditions are true, we get the value in column C, but if either are false, we are multiplying by zero, so the array that is summed by the SUMPRODUCT function, will only include the values in column C, where the first two conditions are true. This is exactly the same as a SUMIFS, except it is case-sensitive!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.