# Shortening OR() with array

#### dafan

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

I have this formula
Code:
=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:
Code:
=SUMPRODUCT((OR('Week 1'!\$B6:\$AN6={"Brett","Michael","Stefan","Casual"})*('Week 1'!B8:AN8)))
but to no avail.

Any ideas? Thanks!

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### pgc01

##### MrExcel MVP
Hi Stefan

=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

##### Well-known Member
Works great, thanks!

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

#### pgc01

##### MrExcel MVP
You're welcome.

An alternative could be:

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

#### dafan

##### Well-known Member
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

##### MrExcel MVP
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)

*Makes notes.

Cheers!

Replies
5
Views
1K
Replies
4
Views
649
Replies
4
Views
753
Replies
1
Views
292
Replies
2
Views
257

1,190,627
Messages
5,982,011
Members
439,751
Latest member
megaman777

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back