dynamic sum formula

Hi

See attached file please. In col H rows 3-6 I have done a manual sum of eggs , bread and cheese for store 1,2 and 3.
I would like this to be done automatically via a formula. So formula would need to recognise which store in col b want total for i.e.
store1, store2 or store3 and then sum up the figures, for the store in colC and record the total for respective store for in colH.

Note for store3, I have on purpose left out milk from the sum for store3 (80) to indicate the problem I have at the moment.
Which is when items are added or taken away from a store I have to adjust the formula manually, to account for the extra , in this case milk ,
so correct total for store3 is 135.

See the attached with changed layout:

=SUMPRODUCT(($C$6:$E$9)*($C$5:$E$5=$G3))


Think very carefully about organising your data.

Originally Posted by JohnTopley

See the attached with changed layout:

=SUMPRODUCT(($C$6:$E$9)*($C$5:$E$5=$G3))


Think very carefully about organising your data.

Hi,

that worked great, and better organisation of data. Many Thanks.

Originally Posted by FDibbins

Are you set on how you have the data laid out? If you change the layout a bit, this will be simple using SUMIFS()

Thank you for response. No data layout is not set. Please show how using sumifs.

Originally Posted by avk

One way ...
Define name manager Store1 in refers to enter formula

Formula:

Please Login or Register  to view this content.

and in cell "H3" formula

Formula:

Please Login or Register  to view this content.

Same for store2 & store3 (change range accordingly)

Thanks good idea

Originally Posted by Glenn Kennedy

Your data layout is horrible, as pointed out... it would be better to use reorganised data. However, it is possible that you are stuck with that layout. if so: use this (with a minimal change in layout - addition of a term at the end of the data)

=IFERROR(SUM(INDEX($C$5:$C$25,MATCH(G3,$B$5:$B$25,0)):INDEX($C$5:$C$25,MATCH(G4,$B$5:$B$25,0))),"")

Thank you. I see answer is correct but could you please explain where in the index match it knows only to count for store1 eggs bread cheese then store2 etc?

Originally Posted by nflsales

or
h2=SUMPRODUCT((LOOKUP(ROW($B$5:$B$21),ROW($C$5:$C$21)/($C$5:$C$21=""),$B$5:$B$21)=$G3)*($C$5:$C$21))
Try this and copy towards down

could you please give some notes as to how the logic in formula is working

Originally Posted by Glenn Kennedy

This bit does that:

MATCH(G3,$B$5:$B$25,0)) looks for the name "store1" in column B and the INDEX portion (INDEX($C$5:$C$25) returns the corresponding value in column C. Excel then sums column C from there until it finds a match in column B for store2 - MATCH(G4,$B$5:$B$25,0).

The term end was added simply to provide a stopping point for summing the data for the last store in the list.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

many thanks for explanation I have added to rep. and clicked solved thread