ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   SUMPRODUCT Formula (https://www.excelbanter.com/excel-discussion-misc-queries/203768-sumproduct-formula.html)

Kay
SUMPRODUCT Formula
 
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[_3_]
SUMPRODUCT Formula
 
=SUMPRODUCT((A2:A100={"Q1","Q2","Q3"})*(E2:E100))

--
__________________________________
HTH

Bob

"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!



Bernie Deitrick
SUMPRODUCT Formula
 
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!



Kay
SUMPRODUCT Formula
 
Thank you so much Bernie and Bob

"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!




Kay
SUMPRODUCT Formula
 
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!




Bernie Deitrick
SUMPRODUCT Formula
 
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" wrote in message
...
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!

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

"Bernie Deitrick" wrote:

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


All times are GMT +1. The time now is 05:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com