COUNTIFS multiple columns
Hi @Paul Newcome I have another interesting scenario I wonder if I can solve with a formula. I have three columns with numbers 1....5 I want to calculate between those three columns anytime a value is >=1 to count as 1 and not every time the value is true. I know I can count the three columns =COUNTIFS([Summer 2019]:[Spring 2020], however if a value appears in all three columns then I'm receiving duplicate counts for the row. And I want the row to only be counted once and not three times if a value appears. I'm trying to say if a value appears at least one time it should return as 1 count not a 3. Hopefully the screenshot below can assist what I'm trying to do.
Any suggestions ideas?
Senior Program Coordinator
De Anza College
Best Answers

I'm not sure I follow. Are you trying to populate a 1 in every row if there is at least one value greater than zero within any one of the three columns?
=IF(COUNTIFS([Summer 2019]:[Spring 2020], @cell > 0) > 0, 1)

Yes. That was my intent. I just forgot to add that part. Sorry about that.

You would want to also use a FIND function to search for [Facilitator Name]@row.
=COUNTIFS({Facilitator '21'22}, FIND([Facilitator Name]@row, @cell) > 0, {Course Name '21'22}, FIND("21SU", @cell) > 0)

It should work. I personally use this regularly where I need to count for a single person within a multicontact type column with multiple contacts in each cell.
Try creating a filter that mimics the ranges/criteria and see what it displays. IS the data in the [Facilitator Name] column the same as what is in the source sheet to include capitalization and spacing?
Answers

I'm not sure I follow. Are you trying to populate a 1 in every row if there is at least one value greater than zero within any one of the three columns?
=IF(COUNTIFS([Summer 2019]:[Spring 2020], @cell > 0) > 0, 1)

You can just add a helper column to the right of this.
=if(sum([Summer 2019]@row:[Spring 2020]@row)>0,1)
Then sum the helper column. Doing this in a single formula would be rather difficult.

@Paul Newcome I'm trying to get the row to just be counted once if a number appears in any of those columns
I think the helper column is what will be needed I kinda figured that was the solution but was trying to avoid doing that. Although I guess I could just hide that helper column. I'll consider looking into that further.
Thanks
Senior Program Coordinator
De Anza College

Ha. I found a way.
=count([summer 2019]:[summer 2019])countifs([summer 2019]:[summer 2019],0,[fall 2019]:[fall 2019],0,[spring 2020]:[spring2020],0)
Pulling the inverse and subtracting it from the total.

@L@123 Not sure I understand your formula I'm receiving and an error #incorrect argument set and its unclear to me as well
Senior Program Coordinator
De Anza College

@Paul Newcome Your formula worked but I did create a hidden column then I counted the entire column and that worked!
Thanks
Senior Program Coordinator
De Anza College

Yes. That was my intent. I just forgot to add that part. Sorry about that.

@Paul Newcome when I use this formula =COUNTIFS({Facilitator '21'22}, [Facilitator Name]@row, {Course Name '21'22}, FIND("21SU", @cell) > 0) to count if 2 variables are met, the formula returns 0 which leads me to believe that a contact list with more then one name in the field won't count unless there is one name that appears in the cell is that correct? Or is there another way to get this to count where I'm not having to write a specific FIND formula for each cell? Especially since I have over 60 contacts to count.
Senior Program Coordinator
De Anza College

You would want to also use a FIND function to search for [Facilitator Name]@row.
=COUNTIFS({Facilitator '21'22}, FIND([Facilitator Name]@row, @cell) > 0, {Course Name '21'22}, FIND("21SU", @cell) > 0)

@Paul Newcome it's still returning 0 which leads me to believe that it wont count if there are multiple names in the contact list?
Senior Program Coordinator
De Anza College

It should work. I personally use this regularly where I need to count for a single person within a multicontact type column with multiple contacts in each cell.
Try creating a filter that mimics the ranges/criteria and see what it displays. IS the data in the [Facilitator Name] column the same as what is in the source sheet to include capitalization and spacing?

OK that was weird a couple refresh to the sheet and now it works, golden!!!
Thank you I will share this formula with a coworker as well who was having issues.
Thanks as always!!!
Senior Program Coordinator
De Anza College
Help Article Resources
Categories
Check out the Formula Handbook template!