Countifs And/Ors

Gladys Liley

I am trying to count a specific series:

Region1 + StatusComplete + Month series

=COUNTIFS({Central HUB – 2019 Range 1}, [Primary Column]@row, {Central HUB – 2019 Range 3}, AND(OR(@cell = "2020-01")), {Central HUB – 2019 Range 3}, AND(OR(@cell = "2020-02")))

Formula only works with 1 Month, once I add the second month, it returns 0.

Any suggestions?

Thank you.

Aya

Hi Gladys—Try this one:

=COUNTIFS({Central HUB – 2019 Range 1}, [Primary Column]@row, {Central HUB – 2019 Range 3}, AND(OR(@cell = "2020-01", @cell = "2020-02")))

Since you have referenced '{Central HUB – 2019 Range 3}' as your 2nd range, you can just list all the logical expression for your AND/OR and not reference it again.

Aya

Aya

Hi Gladys—Try this one:

=COUNTIFS({Central HUB – 2019 Range 1}, [Primary Column]@row, {Central HUB – 2019 Range 3}, AND(OR(@cell = "2020-01", @cell = "2020-02")))

Since you have referenced '{Central HUB – 2019 Range 3}' as your 2nd range, you can just list all the logical expression for your AND/OR and not reference it again.

Aya

Gladys Liley

Thank you so much. Good to know that i was somewhat on track 😉

Aya

You're welcome! 🙂

Aya

Stacy Meadows

I am trying to do something similar on the summary of a sheet....

I want to count if proposed start date column is less than or equal to today, as well as if the PC 2 column = one of "S1", "S2", "S3","S4","S5","S6","S7","S8","S9".

I attempted by just trying "S1","S2","S3"....

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], AND(OR(@cell= "S1", @cell= "S2", @cell= "S3")))

The above did not work so I tried the below:

=COUNTIFS([Proposed Start Date]:[Proposed Start Date], <=TODAY(), [PC 2]:[PC 2], AND(OR([PC 2]:[PC 2] = "S1", [PC 2]:[PC 2] = "S2", [PC 2]:[PC 2] = "S3")))

Any suggestions?? please 😊