Combining countifs and countif into one formula

Susan L.

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!

Hollie Green

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"))

Susan L.

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

Genevieve P.

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

Susan L.

@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!

Genevieve P.

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

Susan L.

@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!

Genevieve P.

No problem! I'm glad we were able to get both data points for you 🙂