DSUM with dynamic criteria?

The Googles have done nothing for me as far as this question goes, so I come to you all on my knees, begging for some clarity.

I have a large spreadsheet of data, with two columns containing the criteria I'd like to use to sum the rest of the data columns. Call these columns "Parlour" and "Toppings". The data columns show the number of parlours of a certain type that offer the corresponding topping for that row. Hence, there might be 5 Pizza Huts that offer Pineapple in one city (column), but only 2 Pizza Huts that offer Goat Cheese.

On another sheet in the workbook, I have a list of parlours, and I want to know, for each parlour, how many locations in a given city offer a certain combination of toppings. My thought was to create a criteria range for the toppings, and a separate list of the parlours, so that the DSUM criteria would merge the ranges somehow. The idea would be to build in the ability to pull the formula down for any parlours that are added to the list, so that the toppings criteria stays the same, but the parlour criteria range is referenced at an offset position, corresponding to the new listing.

Does this make sense? I can't seem to figure out how to modify the criteria field in the DSUM formula to take an array created with statements in it.

Any ideas? All suggestions welcome, and thanks!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

What exactly are the desired results in your sample?

Does this work for you?

=DSUM($B$2:$G$27,$L$3,$I$3:$I4)-SUM(M$3:M3)

copied down

Hello, see the attached.

make a list of Topping items & use criteria as grey highlighted. Hope this helps.

I4:I8, you have Topping items,

Goat Cheese
Steak
Onion
Mushroom
Marshmallow

M4:M8, Parlours.

J5 formula,

=$B3=INDEX(M:M,ROW())

ROW() will give current row #, here is 5 J5. So INDEX(M:M,5) = Domino's. But when you use this N4, it calculate INDEX(M:M,ROW()). here current row is 4, so ROW() will 4, INDEX become,

=INDEX(M:M,4) = Pizza Hut

When you copy down, ROW() calculate, 5, 6, 7, 8.... which is Domino's, Little Caesar's, Pizza Ranch, etc...

K5, formula

=ISNUMBER(MATCH($C3,$I$4:$I$8,0))

If any of the values in C3 to down contains in I4:I8 will count it. So when use in N4, it calculte

1. If B3 to down = Value in current_Row in M:M, which is M4. Pizza Hut

AND

2. If C3 to down = Any of the value in I3:I8 then SUM col_D3 to down.

Hope this help you.

There is also the Pivot Table route which is efficient also....

Hi Haseeb,

I was looking for a way to use Database functions dynamically & came across this post, where you've provided an example Excel sheet doing exactly what I'm looking to achieve. My challenge is whenever I enter the edit mode of the formula & exit using a simple enter (no CSE), the formula starts showing the same value across all cells. I re-opened the file to see if you had array-entered the formula which wasn't the case. Can you please help me understand what am I doing wrong in trying to replicate the formula?

Regards,
Kartar

<Update: Just realized it only happens in version 2003 (maybe lower), works fine in 2007. Is it because from 2007 onwards, MS has allowed the row() function to remain dynamic if used as part of another formula?>

Hello Kartar, welcome to the forum!

Please take a few minutes tom read Forum Rules. Rule # 2 says:

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread

So please start your own thread