Creating a count using COUNTIF across multiple columns
Hi,
i would like to count the number of yes's in a column with a corresponding service line in a separate services column
my first column has three drop down options yes/no/NA and i would like to be able to count each one
So for example:
M&E yes = 37
M&E No = 12
M&E NA = 2
Answers
-
You would have to set up individual COUNTIFS for each column and then add them all together, or...
Insert 3 helper columns (one for each answer) and then use a COUNTIFS on every row to count how many of each answer for each row. Then to get your totals you would use a SUMIFS summing these helper columns based on the Service.
-
Hi Paul,
Thank you for your response.
I need each individual sum I'm not concerned with the total combined just i need to see the breakdown of Yes/No/NA
How do i write a formula for summing the Yes for a Service Live column and it to only pick up the M&E from the Service column.
Apologies if I'm not getting it so far.
-
Right. You would create 3 columns. One for Yes, one for No, and one for N/A. In these columns you would use a COUNTIFS looking from the first column to the last column and counting how many are (for example) "Yes".
Then to get the total number of Yes entries for M&E you would SUMIFS the Yes column where the Service column is "M&E".
-
Please could you show me the formula for this Paul?
I have 11 different category types and need to establish the yes, no, N/A for each one.
-
In the Yes column on the source sheet:
=COUNTIFS([First Column]@row:[Last Column]@row, @cell = "Yes")
ON your separate metrics sheet:
=SUMIFS({Yes Helper Column}, {Category Column}, @cell = "M&E")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!