I don't know anything about SUMPRODUCT - never used it (though I see it
mentioned here often enough that I guess it's about time I learned) - so I
don't want to interfere with what T Valvo told you. But I will add this:
Depending on what's actually in one of those menu-day sheets, it seems to me
you may want SUMIF instead of COUNTIF.

I think repeat think you're saying, below, that in column B of each day-menu
sheet you have an ingredient description such as "Greens + (Veggie
Supplement) - 3 Tsp". In column C is how many of column B are used in
today's menu. Over in the Grocery sheet, column B has a complete list of
possible ingredient descriptions; in C is how many units go into a serving of
column B, and in column D is the unit, eg "tsp", "qt", "fl oz" or whatever.

Now, if what you want is to sum up how many units of B are used in a week's
worth of menus, I think COUNTIF is wrong. COUNTIF can tell you how many
times "Greens + (Veggie Supplement) - 3 Tsp" appeared in this week's menu,
but it counts each occurrence as just one, and you want to take into account
their quantify - that is, if column C was a 3 for Tue, you want it counted as
3 uses, not just one appearance.

For that, I'd use SUMIF, which acts like COUNTIF but needs an extra argument
to tell Excel what range to sum up. Like this:

=SUMIF(Sunday!B$20:B86,B3,Sunday!C20:C86)

This tells Excel to take the value in B3 and search for it in Sunday!B for
that value (eg "Greens + (Veggie Supplement) - 3 Tsp"), and wherever it
appears, add up the values in the correspending cell(s) of Sunday!C. So far,
so good; you can add these up for each sheet however you like, using helping
columns as I mention in a previous post or manually as you were doing before,
or in some other way. But there's a remaining issue here, maybe: Is it
important that you track units? I mean, do you have some of these items in
varying units, in tsps on SundayB23 but in cups on Wednesday!B41? If so, you
need some way of dealing with that.

--- "scottia" wrote:
In the example below I am using Sunday only but need to be able to update
all menu sheets (sat-sun). If I increase Servings from Sunday it should Sum
all servings used for the week in the Grocery List. However I can't seem to
come up with a way to sum servings for all days as they are used in the Menu
Sheets. I can get it to count individually to update servings of 1 for all
days but if I increase a serving to 2 for any food type it only counts it as
1. If I can get an accurate serving count my formuls for Used from Grocery
List will be more accurate.

Grocery List Sheet --

Food "Column B" Used "Column C" Measure "Column D" Servings
Greens + (Veggie Supplement) - 3 Tsp. 8 Tsp 1

Menu Sheet "Sunday" --

Food "Column B" Serving "Column C"
Greens + (Veggie Supplement) - 3 Tsp. 1

I changed my formula earlier tonight in "Used" from Grocery List as follows
to clean things up a little bit. I now get an accurate count across all days
for any menu items selected from the menu sheets including duplicate values
within the same day:
=SUMPRODUCT(--(Sunday!$B$20:$B$86=B183)+(Monday!$B$20:$B$86=B183 )+(Tuesday!$B$20:$B$86=B183)+(Wednesday!$B$20:$B$8 6=B183)+(Thursday!$B$20:$B$86=B183)+(Friday!$B$20: $B$86=B183)+(Saturday!$B$20:$B$86=B183))