Countifs with Or tidy-up

[horizonflame]

Hi,

Can anyone help condense this please?

=COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"01/01/1900")+COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"#")+COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,"No Date")

Thanks

 

[JustynaMK]

Try something like that:

=SUMPRODUCT(--($C$2:$C$3000="GATED"),($G$2:$G$3000=1)+($G$2:$G$3000="#")+($G$2:$G$3000="No Date"))

Please note that I replaced "01/01/1900" with "1", as this is the first registered date in the system.

 

[horizonflame]

Thank you, that did the trick ?

 

[jtakw]

Hi,

Try also this:

=SUM(COUNTIFS(DatesWork!$C$2:$C$3000,"GATED",DatesWork!$G$2:$G$3000,{"01/01/1900","#","No Date"}))

 

[JustynaMK]

You're welcome jtakw's solution will also work perfectly fine. Cheers.