SUM function with LOOKUP to a Dynamic List

[masud8956]

Hi all,

I am having some trouble summing up some data from the dynamic range B2:C15. All the fruit names that populate $B$2:$B$15 belong to to either of the 4 groups. These 4 groups are also dynamic lists.

They are:

Group1: Range $E$3:$E$15,
Group2: Range $F$3:$F$15,
Group3: Range $G$3:$G$15 and
Group4: Range $H$3:$H$15,

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H1[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[TD="align: center"]FRUIT[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GROUP1[/TD]
[TD="align: center"]GROUP2[/TD]
[TD="align: center"]GROUP3[/TD]
[TD="align: center"]GROUP4[/TD]
[/TR]
[TR]
[TD="align: center"]A3[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Banana[/TD]
[TD="align: center"]Grape[/TD]
[TD="align: center"]Date[/TD]
[/TR]
[TR]
[TD="align: center"]A4[/TD]
[TD="align: center"]Lemon[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Mango[/TD]
[TD="align: center"]Peach[/TD]
[TD="align: center"]Lemon[/TD]
[/TR]
[TR]
[TD="align: center"]A5[/TD]
[TD="align: center"]Cherry[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Avocado[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A6[/TD]
[TD="align: center"]Coconut[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cherry[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Coconut[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]A15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I want to sum up the values from B2:C15 in the following style:

Column C should show types of fruit and Column D should sum up the prices group wise.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"]A15[/TD]
[TD="align: center"]B15[/TD]
[TD="align: center"]C15[/TD]
[TD="align: center"]D15[/TD]
[/TR]
[TR]
[TD="align: center"]A16[/TD]
[TD="align: center"]GROUP[/TD]
[TD="align: center"]FRUIT[/TD]
[TD="align: center"]PRICE[/TD]
[/TR]
[TR]
[TD="align: center"]A17[/TD]
[TD="align: center"]GROUP1[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]A18[/TD]
[TD="align: center"]GROUP2[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[/TR]
[TR]
[TD="align: center"]A19[/TD]
[TD="align: center"]GROUP3[/TD]
[TD="align: center"]Cherry, Coconut[/TD]
[TD="align: center"]150[/TD]
[/TR]
[TR]
[TD="align: center"]A20[/TD]
[TD="align: center"]GROUP4[/TD]
[TD="align: center"]Lemon[/TD]
[TD="align: center"]60[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone please help with a suitable Formula?

Thanks in advance!

 

[masud8956]

Wow!

Thanks very much!

 

[XOR LX]

You're welcome!

Cheers

 

[masud8956]

Hi @XOR LX again,

I am sorry I did not see this yesterday.

The formula works fine to lists with simple define name technique.

To keep the lists dynamic I am udefining names with following formula:

=OFFSET('Sheet1'!$E$3,0,0,0,COUNTA('Sheet1'!$E$3:$E$15))

.....and now the formula returns zero value.

What did I do wrong?

 

[XOR LX]

INDIRECT doesn't work with dynamically-defined Named Ranges.

There are workarounds available, though can I first ask if you really need to make those lists dynamic?

Regards

 

[masud8956]

Yes sir.

I need those lists to be dynamic.

Thanks!

 

[XOR LX]

For a start, I prefer to avoid the volatile OFFSET when defining dynamic ranges. I would use:

=Sheet1!$E$3:INDEX(Sheet1!$E$3:$E$15,MATCH("zzz",Sheet1!$E$3:$E$15))

etc.

You then have two options:

Option 1

Remove the INDIRECT references altogether (thus removing all volatility).

=SUMPRODUCT(SUMIF(B$3:B$6,GROUP_1,C$3:C$6))

etc.

Obviously this requires you to manually hard-code the group names within each formula, though has the advantage that it removes the volatile INDIRECT.

Option 2

Use the EVALUATE function, viz:

With the active cell somewhere in row 17, go to Name Manager and define:

Name: Formula1
Refers to: =EVALUATE(SUBSTITUTE(Sheet1!$B17," ","_"))

Exit Name Manager.

The formula in D17 then becomes:

=SUMPRODUCT(SUMIF(B$3:B$6,Formula1,C$3:C$6))

and copied down.

The only slight drawback to this approach is that, although you have not entered any VBA 'proper', the use of EVALUATE within Name Manager still requires that your workbook be saved as macro-enabled.

Regards

 

[masud8956]

You then have two options:........

I am using Option 1. Working perfectly.

Option 2 is equally good as my workbook is already macro-enabled.
Thank you very much for taking the time and explaining 2 beautiful options! Learned a lot from you.

 

[XOR LX]

You're very welcome!

Cheers!