Sheet Summary box COUNTIFS + AND/OR

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 

😊



Paul McGuinness

Hi @Stacy Meadows

You did have it, was just inclusion of the AND statement, as the COUNTIFS requires all criteria to be met to count so the AND isn't needed.

Try this

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

Hope that helps

Thanks

Paul

Paul McGuinness

Hi @Stacy Meadows

You did have it, was just inclusion of the AND statement, as the COUNTIFS requires all criteria to be met to count so the AND isn't needed.

Try this

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

Hope that helps

Thanks

Paul

Stacy Meadows

@Paul McGuinness Thank you so much that worked a treat 😘