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’

Purveyor	Product	Total Need	Breakfast Light
	Eggs, Liquid (Case)	#VALUE!	0.50

<tbody>
</tbody>

Sheet2: ‘Breakfast – Light’

Product	Need
Orange Juice (Case)	0.31
Cranberry Juice (Case)	0.16
Assorted Mini Pastries (Case)	0.17
Butter Balls (Case)	0.03
Coffee Regular (Case)	0.06
Coffee Decaf (Case)	0.05
Assorted Tea Forte Pouches
Bagels (Dozen)
Cream Cheese Packets (Case)
Assorted Muffin Mix (Case)
Breakfast Tacos
Eggs, Liquid (Case)	0.17
Tortillas (Packet)	2.19
Cheese Shred (Case)	0.33
Proteins (Case)	0.33
Ham Egg and Cheese Croissants
Croissants (Case)	0.83
Eggs, Liquid (Case)	0.17
Ham (Case)	0.21
Cheese, American (Packet)	0.25
Sausage Egg and Cheese Biscuits
Sausage Pattie (Case)	0.25
Eggs, Liquid (Case)	0.17
Cheese, American (Packet)	0.25
Biscuits (Case)	0.17

<tbody>
</tbody>

 

[Aladin Akyurek]

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 summary="" items="" bqt="" c2="">
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Breakfast - Light&"'!B:B"),INDIRECT("'"&Breakfast - Healthy&"'!A:A"),B2))

A2: Breakfast - Light
A3: Breakfast - Traditional

etc. These are sheets with data.

Select the range that starts with A2, name the selection SheetList using the Name Box, and run the suggested SumProduct formula.

</cell>

 

[homebrewhero]

A2: Breakfast - Light
A3: Breakfast - Traditional

etc. These are sheets with data.

Select the range that starts with A2, name the selection SheetList using the Name Box, and run the suggested SumProduct formula.

So based on what I've read it sounds like the sheets in excel are identified similarly to cells on a sheet, 'Breakfast - Healthy' would be A5. Again, I just want to make sure I am understanding how this works; INDIRECT references each cell in spreadsheets from 'Breakfast - Light' to 'Breakfast - Healthy' to see if An matches the criterion of B2, "Eggs, Liquid (Case)", in 'BQT Items Summary', and if that is the case, SUMIFS adds together the values one spreadsheet at a time, then those numbers are added together via SUMPRODUCT?

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

 

[homebrewhero]

So based on what I've read it sounds like the sheets in excel are identified similarly to cells on a sheet, 'Breakfast - Healthy' would be A5. Again, I just want to make sure I am understanding how this works; INDIRECT references each cell in spreadsheets from 'Breakfast - Light' to 'Breakfast - Healthy' to see if An matches the criterion of B2, "Eggs, Liquid (Case)", in 'BQT Items Summary', and if that is the case, SUMIFS adds together the values one spreadsheet at a time, then those numbers are added together via SUMPRODUCT?

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

That wouldn't make sense, scratch that last formula, I'm not sure how the SheetList would be replaced as a range?

 

[Aladin Akyurek]

That wouldn't make sense, scratch that last formula, I'm not sure how the SheetList would be replaced as a range?

It seems I succeeded to get you to enter the relevant sheet names (names of the data sheets) in A2:A5 of 'BQT Items Summary'. If so, we'll skip the SheetList parts for it looks like you don't know how to name this range.

With A2:A5 housing the sheet names of interest, invoke:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$5&"'!B:B"),INDIRECT("'"&$A$2:$A$5&"'!A:A"),B2))

 

[homebrewhero]

It seems I succeeded to get you to enter the relevant sheet names (names of the data sheets) in A2:A5 of 'BQT Items Summary'. If so, we'll skip the SheetList parts for it looks like you don't know how to name this range.

With A2:A5 housing the sheet names of interest, invoke:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$5&"'!B:B"),INDIRECT("'"&$A$2:$A$5&"'!A:A"),B2))

It seems you have, only change I have made is the Sheet Names are D2:D5 since I am currently using A for Purveyor. I will have to look up SheetLists in the near future to see if it will simplify things.

Thank you for all of your help Aladin! Truly appreciate it!

 

[Aladin Akyurek]

It seems you have, only change I have made is the Sheet Names are D2:D5 since I am currently using A for Purveyor. I will have to look up SheetLists in the near future to see if it will simplify things.

Thank you for all of your help Aladin! Truly appreciate it!

You are welcome. Thanks for the update.

 

[homebrewhero]

You are welcome. Thanks for the update.

One more question Aladin, not all of the pages will contain the string the formula is searching for. What function would I use to say add 0?

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

I have tried different variations of IF and ISNOT but I can't get a grasp on it, the formula above is what is working for me thus far.

 

[Aladin Akyurek]

One more question Aladin, not all of the pages will contain the string the formula is searching for. What function would I use to say add 0?

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

I have tried different variations of IF and ISNOT but I can't get a grasp on it, the formula above is what is working for me thus far.

What result do you get B2 is not available on some pages?

 

[homebrewhero]

One more question Aladin, not all of the pages will contain the string the formula is searching for. What function would I use to say add 0?

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

I have tried different variations of IF and ISNOT but I can't get a grasp on it, the formula above is what is working for me thus far.

I know before this post the names of the tabs were in column D, I have moved them to Column E.

 

[homebrewhero]

I know before this post the names of the tabs were in column D, I have moved them to Column E.

I get #REF error