CountIfs, needing OR against multiple cols?

[HuskyJones]

Hey folks
You brilliantly helped me the other week with this but i've come across an issue.
What I want is
K = General Practice (in primary care)
AI = 0
AR OR AS OR AT = 1

I've got it to work as an AND but this is giving me too many people/tests e.g. AR4 and AT4 both have 1's so this is counted twice, I just need it once.
I'm sure my formula below will make you cringe but here goes...............

=COUNTIFS('Raw Data'!$K$3:$K$1003,"General Practice (in primary care)", 'Raw Data'!$AI$3:$AI$1003, "0", 'Raw Data'!$AT$3:$AT$1003, "1")+COUNTIFS('Raw Data'!$K$3:$K$1003,"General Practice (in primary care)", 'Raw Data'!$AI$3:$AI$1003, "0", 'Raw Data'!$AR$3:$AR$1003, "1")+COUNTIFS('Raw Data'!$K$3:$K$1003,"General Practice (in primary care)", 'Raw Data'!$AI$3:$AI$1003, "0", 'Raw Data'!$AS$3:$AS$1003, "1")

If you can advise how I can get AR or AS or AT that would be AMAZING!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[HuskyJones]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

will do
365 enterprise

 

[Fluff]

Thanks for that
How about

=ROWS(FILTER('Raw Data'!$K$3:$K$1003,('Raw Data'!$K$3:$K$1003="General Practice (in primary care)")*('Raw Data'!$AI$3:$AI$1003=0)*(('Raw Data'!$AT$3:$AT$1003=1)+('Raw Data'!$AR$3:$AR$1003=1)+('Raw Data'!$AS$3:$AS$1003=1))))

 

[HuskyJones]

Thanks for that
How about

=ROWS(FILTER('Raw Data'!$K$3:$K$1003,('Raw Data'!$K$3:$K$1003="General Practice (in primary care)")*('Raw Data'!$AI$3:$AI$1003=0)*(('Raw Data'!$AT$3:$AT$1003=1)+('Raw Data'!$AR$3:$AR$1003=1)+('Raw Data'!$AS$3:$AS$1003=1))))

wow, thanks!!

If I don't want it to equal General Practice (in primary care) will <>General Practice (in primary care) work ?
Also (sorry) how do I stop the #CALC! if it's a zero?

 

[Fluff]

If I don't want it to equal General Practice (in primary care) will <>General Practice (in primary care) work ?

Yes

Also (sorry) how do I stop the #CALC! if it's a zero?

Wrap it in Iferror.

 

[HuskyJones]

Yes

Wrap it in Iferror.

You're a star - thank you muchly - will have a play tomoz (I'm GMT+2)

 

[Fluff]

You're welcome & thanks for the feedback.