SUMPRODUCT with conditions as a range

[dkubiak]

=SUMPRODUCT(--(AS2:AS20000=BA199),--(T2:T20000=BA191:BA197))

I want my second array to refer to a range of cells where the option within the range is OR. So, I want to count the values in AS where it matches BA199 and when T matches BA191 or BA192 or BA193...

Is this possible?

 

[VoG]

Try

=SUMPRODUCT(--(AS2:AS20000=BA199),--ISNUMBER(MATCH(T2:T20000,BA191:BA197,0)))

 

[dkubiak]

That seems to be working great. Do you mind explaining what exactly is happening? I like to understand the functions as best as an amateur can.

 

[VoG]

MATCH(T2:T20000,BA191:BA197,0)

is looking for a 'hit' for values in column T being found in BA191:BA197.

if a 'hit is found the MATCH returns a number so

ISNUMBER(MATCH(T2:T20000,BA191:BA197,0))

returns an array of False/True. The -- coerces that to an array of 0/1 that SUMPRODUCT can process.

 

[dkubiak]

Thank you! There is so much to learn...

 

[bosco_yip]

Or, your formula must have the same range, i.e.

=SUMPRODUCT(--(AS2:AS8=BA199),--(T2:T8=BA191:BA197))

Regards