in the 3rd array of my sumproduct formula, i'd like to have it look for more
then one option. what is the correct way to write this forumla???

=SUMPRODUCT(--(COLUMN1=A1),--(COLUMN2=A2),--('BW COLUMN3=A3 or A4 or
A5),(COLUMN4))



tia!!

One way, indicatively:

=SUMPRODUCT((A1:A5=A1)*(B1:B5=A2)*((C1:C5=A3)+(C1: C5=A4)+(C1:C5=A5)0),D1:D5)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"JustBreathe" wrote:
in the 3rd array of my sumproduct formula, i'd like to have it look for more
then one option. what is the correct way to write this forumla???

=SUMPRODUCT(--(COLUMN1=A1),--(COLUMN2=A2),--('BW COLUMN3=A3 or A4 or
A5),(COLUMN4))

--('BW COLUMN3=A3 or A4 or A5)

Try this:

--(ISNUMBER(MATCH('BW COLUMN3,A3:A5,0)))



--
Biff
Microsoft Excel MVP


"JustBreathe" wrote in message
...
in the 3rd array of my sumproduct formula, i'd like to have it look for
more
then one option. what is the correct way to write this forumla???

=SUMPRODUCT(--(COLUMN1=A1),--(COLUMN2=A2),--('BW COLUMN3=A3 or A4 or
A5),(COLUMN4))



tia!!

Hi,

this might also work for you

=SUMPRODUCT((B1:B100=A1)*(C1:C100=A2)*OR(E1=A3:A5) *(D1:D100))

Adjust as needed.

If this helps please click the Yes button

Cheers,
Shane Devenshire

"JustBreathe" wrote:

in the 3rd array of my sumproduct formula, i'd like to have it look for more
then one option. what is the correct way to write this forumla???

=SUMPRODUCT(--(COLUMN1=A1),--(COLUMN2=A2),--('BW COLUMN3=A3 or A4 or
A5),(COLUMN4))



tia!!

You can't use OR like that.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

this might also work for you

=SUMPRODUCT((B1:B100=A1)*(C1:C100=A2)*OR(E1=A3:A5) *(D1:D100))

Adjust as needed.

If this helps please click the Yes button

Cheers,
Shane Devenshire

"JustBreathe" wrote:

in the 3rd array of my sumproduct formula, i'd like to have it look for
more
then one option. what is the correct way to write this forumla???

=SUMPRODUCT(--(COLUMN1=A1),--(COLUMN2=A2),--('BW COLUMN3=A3 or A4 or
A5),(COLUMN4))



tia!!