Counting data that meets conditions using COUNTA, UNIQUE, and FILTER functions [Google Sheets]
Use multiple functions to count the number of data entries that meet specific conditions.
We will use the following dummy data.
This is a database of orders received.
Serial numbers are assigned to project names.
Some projects are handled by only one department, while others are handled by multiple departments.
Suppose we want to count the number of projects handled by multiple departments from this data.
This was achieved using the following function formula.
=COUNTA(UNIQUE(FILTER(B4:B,E4:E<>100)))
I couldn't solve this problem myself, so I asked in the Slack of the community I belong to, and they taught me the formula above. Thank you!
I am writing this article to deepen my understanding for future learning.
Three functions are used in this formula.
I will break them down one by one to understand them.
[1] FILTER function
First, use the FILTER function to extract project names where column E 'Apportionment Rate' is not 100.
[1]
=FILTER(B4:B,E4:E<>100)
To visualize how the function works, I entered the formula above into cell G5, as shown in the screenshot below.
The extracted project names were returned starting from G5.
However, as per the structure of the original data, the project names are duplicated.
[2] UNIQUE function
Next, use the UNIQUE function to extract only unique values.
The UNIQUE function returns a list of unique values within a specified range.
[2]
=UNIQUE(FILTER(B4:B,E4:E<>100))
[3] COUNTA function
Finally, by wrapping it in the COUNTA function, you can count the number of projects.
[3]
=COUNTA(UNIQUE(FILTER(B4:B,E4:E<>100)))
[Reference Note] Differences between COUNT-related functions
There are many COUNT-related functions such as 'COUNT', 'COUNTA', and 'COUNTBLANK', and it is easy to get confused when including 'COUNTIF' and 'COUNTIFS'.
Here is a brief note on the differences between 'COUNT', 'COUNTA', and 'COUNTBLANK'.
"COUNT" = numbers counts the number of cells containing
"COUNTA" = all values including strings counts the number of cells containing
"COUNTBLANK" = blank counts the number of cells
*Please note that "COUNTA" is designed to count "spaces" as well, so keep that in mind.
(Postscript)
After publishing this article, I was taught an even more convenient function on X, so I have written an article about it. Please check that out as well.