Issue with text in my dsum and match function

[homebrewhero]

Good afternoon everyone! I am having trouble achieving the results I would like to on my spreadsheet. I would like to add the amount of “Eggs, Liquid (Case)” together from Sheet 2 and have the total on Sheet 1 under the column “Total Need” (which should come out to be 0.50). I have fiddled with different variations of sum and index match as well as changing the formats of cells for a while and can’t seem to get past this.

The following is the formula I have for C2 in Sheet 1 where the error ‘#VALUE!’ currently is (stating that ‘the cell currently being evaluated contains a constant’ underlining what is highlighted below).

=DSUM('Breakfast - Light'!A2:B30, MATCH(B2, 'Breakfast - Light'!A2:A30, 1), B2)

Sheet 1: ‘BQT Item Summary’

[TABLE="width: 428"]
<tbody>[TR]
[TD]Purveyor[/TD]
[TD]Product[/TD]
[TD]Total Need[/TD]
[TD] Breakfast Light[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Eggs, Liquid (Case)[/TD]
[TD]#VALUE![/TD]
[TD] 0.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2: ‘Breakfast – Light’
[TABLE="width: 261"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Need[/TD]
[/TR]
[TR]
[TD]Orange Juice (Case)[/TD]
[TD]0.31[/TD]
[/TR]
[TR]
[TD]Cranberry Juice (Case)[/TD]
[TD]0.16[/TD]
[/TR]
[TR]
[TD]Assorted Mini Pastries (Case)[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]Butter Balls (Case)[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]Coffee Regular (Case)[/TD]
[TD]0.06[/TD]
[/TR]
[TR]
[TD]Coffee Decaf (Case)[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]Assorted Tea Forte Pouches[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bagels (Dozen)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cream Cheese Packets (Case)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assorted Muffin Mix (Case)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Breakfast Tacos[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eggs, Liquid (Case)[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]Tortillas (Packet)[/TD]
[TD]2.19[/TD]
[/TR]
[TR]
[TD]Cheese Shred (Case)[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]Proteins (Case)[/TD]
[TD]0.33[/TD]
[/TR]
[TR]
[TD]Ham Egg and Cheese Croissants[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Croissants (Case)[/TD]
[TD]0.83[/TD]
[/TR]
[TR]
[TD]Eggs, Liquid (Case)[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]Ham (Case)[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]Cheese, American (Packet)[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]Sausage Egg and Cheese Biscuits[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sausage Pattie (Case)[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]Eggs, Liquid (Case)[/TD]
[TD]0.17[/TD]
[/TR]
[TR]
[TD]Cheese, American (Packet)[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]Biscuits (Case)[/TD]
[TD]0.17[/TD]
[/TR]
</tbody>[/TABLE]

 

[homebrewhero]

My apologies, the error when I do 'Show Calculation Steps' is Italics and Underlined.

=DSUM('Breakfast - Light'!A2:B30, MATCH(B2, 'Breakfast - Light'!A2:A30, 1), B2

 

[Aladin Akyurek]

Care to explain what this DSUM formula is supposed to achieve?

By the way, a SUMIFS formula yields 0.51 for Eggs, Liquid (Case), the criterion value in B2.

 

[homebrewhero]

Care to explain what this DSUM formula is supposed to achieve?

By the way, a SUMIFS formula yields 0.51 for Eggs, Liquid (Case), the criterion value in B2.

Thank you for your response Aladin. I toyed with sumifs and was given the value of 0 and that was as far as I got with that function (would very much like to know how you got that answer), then I found dsum and that was the last formula I toyed with before posting. I would like for the function (whichever and however works at this point) to add up all of the 'Need' numbers for specified items, in this case Eggs, Liquid (Case) on row 2, the next item whatever I may have it be on row 3.

The way Sheet 2 is currently set up is that there is another column E that has 'yes' and 'no' drop down boxes on it for Breakfast Tacos and Breakfast Sandwiches. if 'yes' is selected, then there is a formula in the cells of column B to figure out how much of each item is needed for Breakfast Tacos and Breakfast Sandwiches for a certain number of people eating (there is a formula in every cell of column B that has a food item on it that row); if column E is 'no', column B stays blank.

 

[Aladin Akyurek]

Care to post the formula you have in column B?

 

[homebrewhero]

They vary depending on the item, but they are simple maths such as the following

Eggs, Liquid (Case) =f6/150

Where f6 is the cell that holds the user input value of how many people for the Breakfast - Light menu.
The following would be a couple of items for Breakfast Tacos, which has the options with 'yes' or 'no' in column E (underlined rows)

Eggs, Liquid (Case) =IF(E14 = "yes", F6/150,"")
Tortillas (Packet) =IF(E14 = "yes", F6/20*1.75,"")

 

[Aladin Akyurek]

They vary depending on the item, but they are simple maths such as the following

Eggs, Liquid (Case) =f6/150

Where f6 is the cell that holds the user input value of how many people for the Breakfast - Light menu.
The following would be a couple of items for Breakfast Tacos, which has the options with 'yes' or 'no' in column E (underlined rows)

Eggs, Liquid (Case) =IF(E14 = "yes", F6/150,"")
Tortillas (Packet) =IF(E14 = "yes", F6/20*1.75,"")

Since this creates either a blank or a true number, the SUMIFS formula should work also in your workbook: https://dl.dropboxusercontent.com/u/65698317/homebrewhero SUMIFS.xlsx.

 

[homebrewhero]

Since this creates either a blank or a true number, the SUMIFS formula should work also in your workbook: https://dl.dropboxusercontent.com/u/65698317/homebrewhero SUMIFS.xlsx.

Thank you for the formula, it works like a charm, Thank you for all of your help.

One more question, how would I rewrite the formula to work across multiple spreadsheets? Is it just a matter of adding new criteria for each spreadsheet in the formula? Is there a limited number of criteria (or any limit at all) I may sum up in this function? I have about 40 spreadsheets in my workbook.

 

[Aladin Akyurek]

Thank you for the formula, it works like a charm, Thank you for all of your help.

For the record, the formula is:

=SUMIFS('Breakfast – Light'!B:B,'Breakfast – Light'!A:A,B2)

One more question, how would I rewrite the formula to work across multiple spreadsheets? Is it just a matter of adding new criteria for each spreadsheet in the formula? Is there a limited number of criteria (or any limit at all) I may sum up in this function? I have about 40 spreadsheets in my workbook.

If I'm reading your intent correctly:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!B:B"),INDIRECT("'"&SheetList&"'!A:A"),B2))

would create a total across multiple sheets which are listed in SheetList for the criterion B2 houses.

SheetList is thus the name of the range which houses the relevant sheet names like Breakfast – Light.

 

[homebrewhero]

For the record, the formula is:

=SUMIFS('Breakfast – Light'!B:B,'Breakfast – Light'!A:A,B2)


If I'm reading your intent correctly:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!B:B"),INDIRECT("'"&SheetList&"'!A:A"),B2))

would create a total across multiple sheets which are listed in SheetList for the criterion B2 houses.

SheetList is thus the name of the range which houses the relevant sheet names like Breakfast – Light.

Yes, SUMIFS is the function I used, I found it on the spreadsheet you attached.

I want to make sure I understand this correctly, the SUMPRODUCT you used would look something like the following as an example and gather up information regarding to criterion B2 of 'BQT Items Summary' from all of the sheets in-between the two sheets in the formula

given sheets {BQT Items Summary, Breakfast - Light, Breakfast - Traditional, Breakfast - SXSW, Breakfast - Healthy}

<Cell C2 BQT Items Summary>
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Breakfast - Light&"'!B:B"),INDIRECT("'"&Breakfast - Healthy&"'!A:A"),B2))