Seemingly Easy Formula For Countifs on a Rollup Sheet With OR...

Tabitha W.

I'm working on a rollup sheet where I want to count two instances in the same column.

I am currently counting one instance and the formula works fine. When I try to add the second instance it returns unparseable. See below:

Current Working Formula:

=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle (v1) Range 7}, "Baltimore")

New Unparseable Formula:

=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle (v1) Range 7}, "Baltimore"), OR(=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle (v1) Range 7}, "Baltimore Temp")))

Any help on getting the new formula to work in counting both instances of Baltimore and Baltimore Temp would be appreciated!

Paul Newcome

Try this one...

=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle (v1) Range 7}, OR(@cell = "Baltimore", @cell = "Baltimore Temp"))

Paul Newcome

Yes. You would use the OR function with @cell references as the criteria for that particular range.

Paul Newcome

Try this one...

=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 5}, "1", {FY21 PEP Dec 20 Cycle (v1) Range 7}, OR(@cell = "Baltimore", @cell = "Baltimore Temp"))

Tabitha W.

...amazing, thank you (it worked)!

Paul Newcome

Happy to help. 👍️

Tabitha W.

As a follow up, would I just apply the same logic to this formula to get it to count both Baltimore and Baltimore temp:

=COUNTIFS({FY21 PEP Dec 20 Cycle (v1) Range 7}, "baltimore", {FY21 PEP Dec 20 Cycle (v1) Range 6}, ISBLANK(@cell), {FY21 PEP Dec 20 Cycle (v1) Range 5}, "1")

Paul Newcome

Yes. You would use the OR function with @cell references as the criteria for that particular range.