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,

A1	B1	C1	D1	E1	F1	G1	H1
A2	FRUIT	PRICE		GROUP1	GROUP2	GROUP3	GROUP4
A3	Apple	50		Apple	Banana	Grape	Date
A4	Lemon	60		Orange	Mango	Peach	Lemon
A5	Cherry	70				Avocado
A6	Coconut	80				Cherry
A7						Coconut
A8
A9
A10
A11
A12
A13
A14
A15

<tbody>
</tbody>

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.

A15	B15	C15	D15
A16	GROUP	FRUIT	PRICE
A17	GROUP1	Apple	50
A18	GROUP2	-	-
A19	GROUP3	Cherry, Coconut	150
A20	GROUP4	Lemon	60

<tbody>
</tbody>

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!