Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE
Here’s how you can perform mathematical operations when dealing with Boolean (TRUE or FALSE) values in a cell, for example to return count, sum and average values.
Notice in the formulas that the TRUE and FALSE criteria are not enclosed in double quotes.
In Picture #1, the count formula in cell F6 that returns 3 is
=SUMPRODUCT((A2:A18="North")*(C2:C18=TRUE))
.
In picture #2, the sum formula in cell F10 that returns 45 is
=SUMPRODUCT((A2:A18="East")*(C2:C18="")*B2:B18)
.
Notice that a pair of double quotes is used to convey an empty string for no Discount.
In Picture #3, the average formula in cell F14 that returns 20 is
=AVERAGE(IF((A2:A18="South")*(C2:C18=FALSE),B2:B18))
.
Recall, array formulas are applied to a cell by pressing Ctrl+Shift+Enter, not just Enter.
The curly braces are entered automatically by Excel; you do not enter them yourself.
Thanks a lot!