Shortening OR() with array

[dafan]

Hi all, quick question (can't google it, OR & array give too much hits on Array formulas).

I have this formula

=SUMPRODUCT((OR('Week 1'!$B5:$AN5="Brett",'Week 1'!$B5:$AN5="Michael",'Week 1'!$B5:$AN5="Stefan",'Week 1'!$B5:$AN5="Casual")*('Week 1'!B7:AN7)))

As the range is the same for each logical, I would like to shorten this.
I tried:

=SUMPRODUCT((OR('Week 1'!$B6:$AN6={"Brett","Michael","Stefan","Casual"})*('Week 1'!B8:AN8)))

but to no avail.

Any ideas? Thanks!

 

[pgc01]

Hi Stefan

Please notice that

=Sumproduct(OR()*('Week 1'!B7:AN7))

is the same as

=OR()*Sumproduct(1*'Week 1'!B7:AN7)

You cannot use OR() in array formulas if you expect it to return an array.

Try:

=SUMPRODUCT(ISNUMBER(MATCH('Week 1'!$B5:$AN5,{"Brett","Michael","Stefan","Casual"},0))*('Week 1'!B7:AN7))

This will test for each column if the cell in row 5 hauses ANY of the strings. If this is not what you want, post back.

 

[dafan]

Works great, thanks!

Thanks for the alternative to OR() in SUMPRODUCT, might come in handy later

 

[pgc01]

You're welcome.

An alternative could be:

=SUMPRODUCT(('Week 1'!$B5:$AN5={"Brett";"Michael";"Stefan";"Casual"})*('Week 1'!B7:AN7))

 

[dafan]

Ah that was exactly what i thought it was, but i couldn't get it to work. Didn't know arrays had to be divided by semi-colons instead of comma's.

 

[pgc01]

Didn't know arrays had to be divided by semi-colons instead of comma's.

They don't always.

Arrays divided by commas are horizontal, divided by semicolons are vertical.

Since you want to test EACH of the values of 'Week 1'!$B5:$AN5 with ALL the values of the constant array {}, the arrays must be orthogonal, hence the semicolons.

If your range was a vertical vector, like A1:A10, then the array had to be horizontal, ex:

=SUMPRODUCT((A1:A10={"A","B"})*B1:B10)

 

[dafan]

*Makes notes.

Cheers!