Combining countifs and countif into one formula
Hi all,
I have a sheet which includes two separate formulas, one, a "countifs" and one a "countif". Both work seamlessly in their respective columns. However, in reviewing my related dashboard, I noticed that my "countif" column was including a row of data it shouldn't and realized that I needed to amend my "countif" formula to also include the criteria of my "countifs" formula.
I thought combining the two would be straightforward, but, once again, my enthusiasm has exceeded my ability. I get "your syntax isn't quite right" every...single....time...So, could someone share how to combine the following two formulas into one:
=COUNTIF([Prog Actn]@row, OR(@cell = "MATR", @cell = "PRGC"))
=COUNTIFS([Changed Plan]:[Changed Plan], =0, [Changed Campus]:[Changed Campus], =0, [Deferred Later]:[Deferred Later], =0, [Admit Term]:[Admit Term], OR(@cell = "Fall 2023", @cell = "Summer 2023"))
In essence, I want to count all the people who meet the criteria of "MATR" and "PRGC" AND who also have not changed programs, campuses or deferred for the Summer 2023 and Fall 2023 terms.
With thanks as always!
Answers

If I am understanding correctly I believe the below formula should work.
=COUNTIFS([Changed Plan]:[Changed Plan], =0, [Changed Campus]:[Changed Campus], =0, [Deferred Later]:[Deferred Later], =0, [Admit Term]:[Admit Term], OR(@cell = "Fall 2023", @cell = "Summer 2023",[Prog Actn]:[Prog Actn], OR(@cell = "MATR", @cell = "PRGC"))

@Hollie Green I get an "Invalid Data Type" error with this version...weird....

Hiya! Just jumping in here to say it looks like you're missing a closing parentheses after the first OR statement:
=COUNTIFS([Changed Plan]:[Changed Plan], 0, [Changed Campus]:[Changed Campus], 0, [Deferred Later]:[Deferred Later], 0, [Admit Term]:[Admit Term], OR(@cell = "Fall 2023", @cell = "Summer 2023"), [Prog Actn]:[Prog Actn], OR(@cell = "MATR", @cell = "PRGC"))
Let us know if that fixed it!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. It worked as a "sum" feature! by which I mean it gave the total # of people who matched those criteria in a cell, across all cells of the column. But, because I have a chart widget tied to this column and the results are disaggregated across a couple of different groups of people, I need the formula to produce a 1 or 0 that can be later counted....like this:
All is not lost though, I can use this column to create summary data on my dashboard!

Hi @Susan L.
Yes! That formula is counting the number of rows that match your criteria down all columns  this is because we used a column reference, like so:
[Changed Plan]:[Changed Plan]
If you're just looking to return a 1 or a 0 depending on your criteria, we'd actually use an IF statement for this:
=IF(AND(all the criteria are met in this row), then 1, otherwise 0
Try:
=IF(AND([Changed Plan]@row = 0, [Changed Campus]@row = 0, [Deferred Later]@row = 0, OR([Admit Term]@row = "Fall 2023", [Admit Term]@row = "Summer 2023"), OR([Prog Actn]@row = "MATR", [Prog Actn]@row = "PRGC")), 1, 0)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. That worked! Thank you so much. The "total" formula is also really useful as I've added those values as metrics to my dashboard. I don't know why I didn't reframe this as an "if" statement instead of a "countif/countifs". I appreciate all the help!

No problem! I'm glad we were able to get both data points for you 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!