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!

 

[XOR LX]

Hi,

Do you have access to the TEXTJOIN function (usually requires Excel 2016 with an Office 365 subscription)?

Regards

 

[masud8956]

No I don't. I am using Excel 2010.

 

[XOR LX]

Then unfortunately you'll require some VBA in order to generate the comma-separated strings in cells C17:C20. Are you ok with this?

Regards

 

[masud8956]

I am ready to sacrifice C17:C20 as long as I can aquire D17:D20 using formula.

I prefer a solution wirhout VBA if possible.

Thanks for your quick replies!

 

[masud8956]

And, Sorry for the mistake #1 twice.

I have written B2:C15 when I meant B3:C15. Or B2:C15 with header.

 

[XOR LX]

I am ready to sacrifice C17:C20 as long as I can aquire D17:D20 using formula.

In D17:

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

Assumes that the contents of cell B17 match precisely the Defined Name associated with that group.

Regards

 

[masud8956]

It worked nicely!

Thank you very much!!

 

[masud8956]

Assumes that the contents of cell B17 match precisely the Defined Name associated with that group.

Hi @XOR LX,

Just another query,

If I need to have some different text at B17 other that the defined name. What would be the way? If I define the group with my intended 'text' then a "_" sign appears between every words. A solution to this would really help!

Thanks!

 

[XOR LX]

You mean, for example, that your Defined Name would be e.g. GROUP_1 but you'd have "GROUP 1" in B17? If so, we can simply replace all spaces with underscores within the formula:

=SUMPRODUCT(SUMIF(B$3:B$6,INDIRECT(SUBSTITUTE(B17," ","_")),C$3:C$6))

Regards