SUMPRODUCT and/or

[SaraWitch]

Hello Excel peeps.

I have a formula to add cases based on a couple of criteria. What I can't get the following formula to do is count if either "Drugs, substance misuse and/or drug dealing" or "Alchol related" are entered in the last column (N):

=SUMPRODUCT(--('ASB Cases 2021-22'!$C$6:$C$1002<H2),--('ASB Cases 2021-22'!$C$6:$C$1002>=F2),--('ASB Cases 2021-22'!$L$6:$L$1002="EBC"),--('ASB Cases 2021-22'!$M$6:$M$1002="High"),--('ASB Cases 2021-22'!$N$6:$N$1002="Drugs, substance misuse and/or drug dealing"),--('ASB Cases 2021-22'!$N$6:$N$1002="Alcohol related"))

It works if I do one or the other, but I need to count together if either are selected.

Any help would be appreciated.

Ta muchly!

 

[Marcelo Branco]

Sum the two conditions - this is how you should use an OR condition in SUMPRODUCT

=SUMPRODUCT(--('ASB Cases 2021-22'!$C$6:$C$1002<H2),--('ASB Cases 2021-22'!$C$6:$C$1002>=F2),--('ASB Cases 2021-22'!$L$6:$L$1002="EBC"),--('ASB Cases 2021-22'!$M$6:$M$1002="High"),('ASB Cases 2021-22'!$N$6:$N$1002="Drugs, substance misuse and/or drug dealing")+('ASB Cases 2021-22'!$N$6:$N$1002="Alcohol related"))

M.

 

[SaraWitch]

This is perfect; thank you, @Marcelo Branco!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.