SUMPRODUCT Formula

[kay]

Hi!
I am using sumproduct formula
for example i want to look for Q1 in column A and then sum the value of
Column E for value Q1
=sumproduct(--("Q1"=A2:A100),E2:E100)

My question is that How can i give multiple selection
For eg in above formula
i want to look for q1,q2 and q3
something like
=sumproduct(--("Q1,Q2,Q3"=A2:A100,E2:E100)
OR
=sumproduct(--("Z2:Z4"=A2:A100,E2:E100)

I select Q1,Q2and Q3 from the dropdown list.

Thanks!

 

[Bob Phillips]

=SUMPRODUCT((A2:A100={"Q1","Q2","Q3"})*(E2:E100))

 

[Bernie Deitrick]

kay,

=SUMPRODUCT((ISNUMBER(FIND(A2:A100,"Q1Q2Q3")))*E2:E100)

HTH,
Bernie
MS Excel MVP

 

[kay]

Thank you so much Bernie and Bob

kay,

=SUMPRODUCT((ISNUMBER(FIND(A2:A100,"Q1Q2Q3")))*E2:E100)

HTH,
Bernie
MS Excel MVP

 

[kay]

when i tried for sample number it works
but I have this formula which i am using
=IF($C$11="ALL",SUMPRODUCT(--($C$12=SH2!AK2:AK30370),--("Q2"=SH2!AL2:AL30370),--($C$15<=SH2!AP2:AP30370),--($D$15>=SH2!AP2:AP30370),SH2!O2:O30370),SUMPRODUCT(--($C$11=SH2!C2:C30370),--($C$12=SH2!AK2:AK30370),--("Q2"=SH2!AL2:AL30370),--($C$15<=SH2!AP2:AP30370),--($D$15>=SH2!AP2:AP30370),SH2!O2:O30370)


so what it does if c11 = All, then it sums the value else if c11 = Product1
then look product1 in sh2 from C2 to C30370

so if C11 = Product1,Product2 and when i use your solution it does not
work - gives error, may be i am missing some thing

any help?

Thanks!

 

[Bernie Deitrick]

Kay,

Your formula works fine for me.

What I like to do is test the formula by parts: enter the first sumproduct formula by itself

=SUMPRODUCT(--($C$12=SH2!AK2:AK30370),--("Q2"=SH2!AL2:AL30370),--($C$15<=SH2!AP2:AP30370),--($D$15>=SH2!AP2:AP30370),SH2!O2:O30370)

and then reduce the range to a workbable number of cells, say through row 10

=SUMPRODUCT(--($C$12=SH2!AK2:AK10),--("Q2"=SH2!AL2:AL10),--($C$15<=SH2!AP2:AP10),--($D$15>=SH2!AP2:AP10),SH2!O2:O10)

and put values into those 9 rows that will give you some result that you expect.

Then select each subpart, such as this

--($C$12=SH2!AK2:AK10)

and then press F9 to calculate that part - you will get something like this for each of those --

{0;1;1;1;1;1;1;1;1}

If you don't get 0 or 1 values, then you may have errors in your formula...

Do that for each part, and then take this

{0;1;1;1;1;1;1;1;1}, {0;1;1;1;1;1;1;1;1}

and change the separating comma to a *

{0;1;1;1;1;1;1;1;1}*{0;1;1;1;1;1;1;1;1}

and select all that and press F9 to calc that subsection.

HTH,
Bernie
MS Excel MVP

 

[kay]

ok finally it is working.
Thanks , i really appreciate your help.