{=sum(- -(b8:b550 < f8:f550))}
One more thing I’ll whoop on ya today: Excel’s spooky little double-negative.
S’pose you want to know the number of times something was true, and then do math with it
COUNTIF won’t help because we’re looking for Conditions, as opposed to numbers or discrete criteria.
Excel has lots of Conditional functions that give a TRUE or FALSE. Using “- -” converts TRUE/FALSE into 1 or 0. Now you can Sum, Divide, get a percentage, etc.
The formula above can be used to answer these types of questions:
- Of 542 entries, how many had their ACTUAL exceed their BUDGET?
- How many vacation days did we survive without a feud?
- of 542 students, how many improved on their scores from last semester?
- How many of the 542 shipments arrived on time?
- How many of the 542 registrants actually attended?
All of these can be answered TRUE/FALSE and the “- -” gives you a number, keeping you from having an extra step to tally all your TRUEs or FALSEs.
Keep that data clean y’all!
I know this is four years old, but just in case someone else finds this, thought I’d add another tid-bit to this useful info. Any mathematical operation on the comparison will “convert” TRUE or FALSE to a 1 or 0. The example formula you show could also be written as:
{=sum(1*(b8:b550 < f8:f550))}
or
{=sum(0+(b8:b550 < f8:f550))}
These probably aren't as quick to type as the "–", but just illustrating the point.
Nice. Nice use of an array formula. Thanks for adding to the conversation.