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]

I get #REF error

Looks like one or more sheets do not exist which are named in E2:E16. Could you check this?

 

[homebrewhero]

Eureka, I just noticed that one of the pages had an extra space in it, false alarm, thank you for your input again Aladin!

 

[Aladin Akyurek]

Eureka, I just noticed that one of the pages had an extra space in it, false alarm, thank you for your input again Aladin!

That makes that sheet non-existing. Thanks for the update.

 

[homebrewhero]

That makes that sheet non-existing. Thanks for the update.

Hello again Aladin, What function would work for the following:

Search Column A of 'Breakfast - Light' for the first item/string not already in Column B of 'BQT Summary Items', add that string to Column Br (r being Row) of 'BQT Item Summary' then look for the next item/string not already in Column B of 'BQT Item Summary', add that string to Column B(r + 1). When all of Column A in 'Breakfast - Light' is in Column B of 'BQT Items Summary', do so for all of the next spreadsheets.

Not to complicate it further, but I would also like to have the text 'Product' not be one of the strings if possible.

 

[Aladin Akyurek]

Hello again Aladin, What function would work for the following:

Search Column A of 'Breakfast - Light' for the first item/string not already in Column B of 'BQT Summary Items', add that string to Column Br (r being Row) of 'BQT Item Summary' then look for the next item/string not already in Column B of 'BQT Item Summary', add that string to Column B(r + 1). When all of Column A in 'Breakfast - Light' is in Column B of 'BQT Items Summary', do so for all of the next spreadsheets.

Not to complicate it further, but I would also like to have the text 'Product' not be one of the strings if possible.

Could you make this intelligible by means of a small sample?