Sumproduct - Condition based on lookup of a Lookup

Hi,

I have 5 columns of Data (Column A through E).

I need to sum data in Col E based on satisfaction of conditions in Col
A through D.

Value in Col A should match val in p24.
Value in Col B should match val in p25.
Value in Col C should match val in p26.

(So far so good, I know I could have used sumproduct to solve, but...)

Its the column D which has been a problematic one. The value in P27
corresponds to a small table in Z1:AA10 (Basically P27 might be present
in any one of the cells Z1 through Z10). Now, whenever Cell P27 and
Cells Z1 hrough Z10 matches, then the corresponding value in Column AA
needs to be matched with the Column D values.

How to solve this? (I have 6000 rows of data and I would need to do
sumproduct summarizaton for many cells.)

regards,
HP
India

Try

=SUMPRODUCT(--(A1:A7000=P24),--(B1:B7000=P25),--(C1:C7000=P26),--(D1:D7000=VLOOKUP(P27,Z1:AA10,2,0)),E1:E7000)

Try this:

=SUMPRODUCT((A1:A6000=P24)*(B1:B6000=P25)*(C1:C6000=P26)*(D1:D6000=INDEX(AA1
:AA10,MATCH(P27,Z1:Z10,0)))*E1:E6000)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Hari" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have 5 columns of Data (Column A through E).
>
> I need to sum data in Col E based on satisfaction of conditions in Col
> A through D.
>
> Value in Col A should match val in p24.
> Value in Col B should match val in p25.
> Value in Col C should match val in p26.
>
> (So far so good, I know I could have used sumproduct to solve, but...)
>
> Its the column D which has been a problematic one. The value in P27
> corresponds to a small table in Z1:AA10 (Basically P27 might be present
> in any one of the cells Z1 through Z10). Now, whenever Cell P27 and
> Cells Z1 hrough Z10 matches, then the corresponding value in Column AA
> needs to be matched with the Column D values.
>
> How to solve this? (I have 6000 rows of data and I would need to do
> sumproduct summarizaton for many cells.)
>
> regards,
> HP
> India
>

Try:

=SUMPRODUCT(($A$1:$A$100)=P24)*($B$1:$B$100=P25)*($C$1:$C$100=P26)*($D$1:$D$100=VLOOKUP(P27,$Z$1:$AA$10,2,FALSE))

Change ranges to suit.

HTH

"Hari" wrote:

> Hi,
>
> I have 5 columns of Data (Column A through E).
>
> I need to sum data in Col E based on satisfaction of conditions in Col
> A through D.
>
> Value in Col A should match val in p24.
> Value in Col B should match val in p25.
> Value in Col C should match val in p26.
>
> (So far so good, I know I could have used sumproduct to solve, but...)
>
> Its the column D which has been a problematic one. The value in P27
> corresponds to a small table in Z1:AA10 (Basically P27 might be present
> in any one of the cells Z1 through Z10). Now, whenever Cell P27 and
> Cells Z1 hrough Z10 matches, then the corresponding value in Column AA
> needs to be matched with the Column D values.
>
> How to solve this? (I have 6000 rows of data and I would need to do
> sumproduct summarizaton for many cells.)
>
> regards,
> HP
> India
>
>

.... missed the E1:E100 at he end

=SUMPRODUCT(($A$1:$A$100)=P24)*($B$1:$B$100=P25)*($C$1:$C$100=P26)*($D$1:$D$100=VLOOKUP(P27,$Z$1:$AA$10,2,FALSE)*(E1:E100))

"Toppers" wrote:

> Try:
>
> =SUMPRODUCT(($A$1:$A$100)=P24)*($B$1:$B$100=P25)*($C$1:$C$100=P26)*($D$1:$D$100=VLOOKUP(P27,$Z$1:$AA$10,2,FALSE))
>
> Change ranges to suit.
>
> HTH
>
> "Hari" wrote:
>
> > Hi,
> >
> > I have 5 columns of Data (Column A through E).
> >
> > I need to sum data in Col E based on satisfaction of conditions in Col
> > A through D.
> >
> > Value in Col A should match val in p24.
> > Value in Col B should match val in p25.
> > Value in Col C should match val in p26.
> >
> > (So far so good, I know I could have used sumproduct to solve, but...)
> >
> > Its the column D which has been a problematic one. The value in P27
> > corresponds to a small table in Z1:AA10 (Basically P27 might be present
> > in any one of the cells Z1 through Z10). Now, whenever Cell P27 and
> > Cells Z1 hrough Z10 matches, then the corresponding value in Column AA
> > needs to be matched with the Column D values.
> >
> > How to solve this? (I have 6000 rows of data and I would need to do
> > sumproduct summarizaton for many cells.)
> >
> > regards,
> > HP
> > India
> >
> >

Hi,

Thanks to RD, Toppers and Daddylonglegs for posting solutions.

l am extremely sorry, but I forgot to add one very crucial piece of
information. (I wouldnt protest if I get any brickbats from you)

The data I have in my Z1:AA10 table has repeating values. For example
Z1 and Z6 might have same value (both of which might be equal to P27).
In that case I want the "lookup" to return both AA1 and AA6 and each of
the cells of SumProduct should be checked with both AA1 and AA6.

Why do I have data like this?

Basically, in cells P27 (and Column Z) the data I have is of PRODUCTS
at an aggregate level. On the other hand, Column AA (and Column D) the
data is of products at an Atomic level (broken down or granular level).

Just to give an example, I can have the value "Microsoft Office" in P27
while, Col D and Col AA will always have granular values like "Office
97", "Office 2000", "Office XP" and "Office 2003" etc. Now, sometimes I
might be interested in finding out number of users of Office 2000
(granular) and sometimes the consolidated product like MS office. So, I
created a lookup table (Z1:AA10) in which the Consolidated products
were listed as many times along with the corresponding granular
products and even the granular products were listed with the same
granualr value in AA.

Please assist me in finding a solution to the same.

Regards,
HP
India

Hi,

Thanks to RD, Toppers and Daddylonglegs for posting solutions.

l am extremely sorry, but I forgot to add one very crucial piece of
information. (I wouldnt protest if I get any brickbats from you)

The data I have in my Z1:AA10 table has repeating values. For example
Z1 and Z6 might have same value (both of which might be equal to P27).
In that case I want the "lookup" to return both AA1 and AA6 and each of
the cells of SumProduct should be checked with both AA1 and AA6.

Why do I have data like this?

Basically, in cells P27 (and Column Z) the data I have is of PRODUCTS
at an aggregate level. On the other hand, Column AA (and Column D) the
data is of products at an Atomic level (broken down or granular level).

Just to give an example, I can have the value "Microsoft Office" in P27
while, Col D and Col AA will always have granular values like "Office
97", "Office 2000", "Office XP" and "Office 2003" etc. Now, sometimes I
might be interested in finding out number of users of Office 2000
(granular) and sometimes the consolidated product like MS office. So, I
created a lookup table (Z1:AA10) in which the Consolidated products
were listed as many times along with the corresponding granular
products and even the granular products were listed with the same
granualr value in AA.

Please assist me in finding a solution to the same.

Regards,
HP
India

Maybe someone can come up with something better, but in the mean time, try
this *array* formula for *2* matches in AA1 to AA10:

=SUMPRODUCT((A1:A6000=P24)*(B1:B6000=P25)*(C1:C6000=P26)*((D1:D6000=INDEX(AA
1:AA10,SMALL(IF(Z1:Z10=P27,ROW($1:$10)),1)))+(D1:D6000=INDEX(AA1:AA10,LARGE(
IF(Z1:Z10=P27,ROW($1:$10)),1))))*E1:E6000)

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Hari" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Thanks to RD, Toppers and Daddylonglegs for posting solutions.
>
> l am extremely sorry, but I forgot to add one very crucial piece of
> information. (I wouldnt protest if I get any brickbats from you)
>
> The data I have in my Z1:AA10 table has repeating values. For example
> Z1 and Z6 might have same value (both of which might be equal to P27).
> In that case I want the "lookup" to return both AA1 and AA6 and each of
> the cells of SumProduct should be checked with both AA1 and AA6.
>
> Why do I have data like this?
>
> Basically, in cells P27 (and Column Z) the data I have is of PRODUCTS
> at an aggregate level. On the other hand, Column AA (and Column D) the
> data is of products at an Atomic level (broken down or granular level).
>
> Just to give an example, I can have the value "Microsoft Office" in P27
> while, Col D and Col AA will always have granular values like "Office
> 97", "Office 2000", "Office XP" and "Office 2003" etc. Now, sometimes I
> might be interested in finding out number of users of Office 2000
> (granular) and sometimes the consolidated product like MS office. So, I
> created a lookup table (Z1:AA10) in which the Consolidated products
> were listed as many times along with the corresponding granular
> products and even the granular products were listed with the same
> granualr value in AA.
>
> Please assist me in finding a solution to the same.
>
> Regards,
> HP
> India
>

Hi,

Please let me know, in case what am asking is impossible to achieve
with the standard formulas. I will have to then think of redisigning
the spreadsheet in a major way.

Regards
HP
India

RD,

Thanks for the response.

I put in the formula (by doing CSE) and for a particular product am
getting a value of zero. (it should be non-zero). I used the evaluate
formula feature to step in to it and notice that ROW($1:$10) always
evaluate to a value of 10. I believe that Rows($1:$10) should retrun an
array of values from 1 to 10 out of which depending on whichever of the
cases Z1:Z10=P27, the corresponding row numbers would be returned.
Please let me know in case am wrong in my supposition.

>Maybe someone can come up with something better, but in the mean time, try
>this *array* formula for *2* matches in AA1 to AA10

Does the above statement mean that the present CSE formula would return
correct values only if the smaller Lookup table has atmost 2 repeating
values?


Also, a doubt little unconnected to my goal here. I see that the array
part of IF condition evaluates to True and False and when the number 10
gets multiplied by 10 then False remains as false while True changes to
10. I have 2 questions here:-
a) Why is False not changing to zero when multiplied by 10 but true
changes to 10 when mutliplied by 10
b) When we apply the SMALL function on a set of array values containing
FALSE and some positive numbers, why is the function not returning
False or Zero as the answer. Presently it returns the smallest positive
number.

Regards,
HP
India

RD,

Thanks for the response.

I put in the formula (by doing CSE) and for a particular product am
getting a value of zero. (it should be non-zero). I used the evaluate
formula feature to step in to it and notice that ROW($1:$10) always
evaluate to a value of 10. I believe that Rows($1:$10) should retrun an
array of values from 1 to 10 out of which depending on whichever of the
cases Z1:Z10=P27, the corresponding row numbers would be returned.
Please let me know in case am wrong in my supposition.

>Maybe someone can come up with something better, but in the mean time, try
>this *array* formula for *2* matches in AA1 to AA10

Does the above statement mean that the present CSE formula would return
correct values only if the smaller Lookup table has atmost 2 repeating
values?


Also, a doubt little unconnected to my goal here. I see that the array
part of IF condition evaluates to True and False and when the number 10
gets multiplied by 10 then False remains as false while True changes to
10. I have 2 questions here:-
a) Why is False not changing to zero when multiplied by 10 but true
changes to 10 when mutliplied by 10
b) When we apply the SMALL function on a set of array values containing
FALSE and some positive numbers, why is the function not returning
False or Zero as the answer. Presently it returns the smallest positive
number.

Regards,
HP
India

Try this

=SUMPRODUCT(--(A1:A7000=P24),--(B1:B7000=P25),--(C1:C7000=P26),--ISNUMBER(MATCH(D1:D7000,IF(Z1:Z10=P27,AA1:AA10,""),0)),E1:E7000)

confirmed with CTRL+SHIFT+ENTER

....or given that you need CSE anyway, you might as well dispense with SUMPRODUCT altogether and just use

=SUM((A1:A7000=P24)*(B1:B7000=P25)*(C1:C7000=P26)*ISNUMBER(MATCH(D1:D7000,IF(Z1:Z10=P27,AA1:AA10,"") ,0))*E1:E7000)

confirmed with CTRL+SHIFT+ENTER

Daddylonglegs,

Your formula works perfectly. Thanks for your help

Regards,
HP
India

daddylonglegs wrote:
> Try this
>
> =SUMPRODUCT(--(A1:A7000=P24),--(B1:B7000=P25),--(C1:C7000=P26),--ISNUMBER(MATCH(D1:D7000,IF(Z1:Z10=P27,AA1:AA10,""),0)),E1:E7000)
>
> confirmed with CTRL+SHIFT+ENTER
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=546461