Sumproduct - Condition based on lookup of a Lookup

[Hari]

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

 

[Ragdyer]

Try this:

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

 

[daddylonglegs]

Try

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

 

[Guest]

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

 

[Guest]

.... 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))

 

[Hari]

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

 

[Hari]

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

 

[Ragdyer]

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)*((D16000=INDEX(AA
1:AA10,SMALL(IF(Z1:Z10=P27,ROW($1:$10)),1)))+(D16000=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.

 

[Hari]

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

 

[Hari]

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

 

[daddylonglegs]

Try this

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

confirmed with CTRL+SHIFT+ENTER

 

[daddylonglegs]

.....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(D17000,IF(Z1:Z10=P27,AA1:AA10,"")
,0))*E1:E7000)

confirmed with CTRL+SHIFT+ENTER

 

[Hari]

Daddylonglegs,

Your formula works perfectly. Thanks for your help

Regards,
HP
India