I’m working on a new Excel title right now and I was going to put a note in there about counting the number of TRUE or FALSE entries returned by the EXACT function, but then I realized that there actually isn’t a SUMA function that would let you do that. At least not in Excel 2013.
So I asked myself, how could I take a column of 10,000 entries and find out how many of those entries were TRUE versus FALSE?
You may be thinking to yourself, why would I even need this? When is that going to happen? The scenario I was looking at was using the EXACT function to compare two columns of text to identify any entries where those two columns aren’t the same value (like I do when I’m looking at AMS ad performance). I generally use an IF function for that so I have blank spaces or ERROR as my values, but if you use EXACT instead you’ll have TRUE and FALSE values. And it’s hard to scan down a list like that to count how many FALSE entries you have, if any.
So what can you do if you have a column of 10,000 entries that read TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, etc. etc. and you want to easily count the number of entries that are TRUE or the number that are FALSE?
There are any number of options. You could filter your data. You could sort it. You could create a pivot table even. But how do you do this with a function?
It turns out that Excel assigns values to TRUE and FALSE. TRUE has a value of 1. FALSE has a value of 0. That’s why I thought there should be a SUMA function to tell Excel to sum your TRUE and FALSE entries. But there isn’t.
What there is is the AVERAGEA function which will average the values across a range, including your TRUE and FALSE values. So if all of your entries in a range are TRUE, you’ll get a value of 1 using AVERAGEA. If they’re all FALSE you’ll get a value of 0. If some are TRUE and some are FALSE you’ll get a decimal value representing the proportion of your entries that are TRUE.
It’s very simple to convert that decimal value to a count of TRUE or FALSE entries as long as you know the total number of values you’re averaging.
For TRUE entries it’s number of total values * AVERAGEA for the range.
For FALSE entries it’s number of total values * (1 minus AVERAGEA for the range).
And if you want to get really fancy because you don’t know the number of values you’re dealing with, you can use COUNTA to get the number of values.
So for Column G, for example, if we wanted the number of TRUE entries we’d use:
=COUNTA(G:G)*AVERAGEA(G:G)
COUNTA will count all entries including those with TRUE or FALSE values and then AVERAGEA will average all values in the range, including TRUE and FALSE entries.
This will only work if the entire range consists of TRUE and FALSE values or blank spaces, but it does work.
Like so:
M.L. Humphrey 