Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

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

Picture #1

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.

Picture #2

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.

Picture #3