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" wrote:

kay,

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

HTH,
Bernie
MS Excel MVP


"kay" wrote in message
...
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!