I'm missing something. You have at least 8 tabs: Grocery List, Sunday,
Monday, Tuesday, Wednesday, Thursday, Friday and Saturday. Somewhere on (I
suppose) Grocery List, or maybe in many places on that sheet, you have the
below formula counting how many times the value in B185 appears in B20:C86 in
each of the 7 menu-day sheets: 67 rows long, 2 wide and 7 sheets deep, for
938 cells total. But what's in B185? I guess that's a particular menu item,
like "baking-powder biscuits" or "grilled salmon"? And why two columns in
the days? Are those lunch and supper?

At any rate, the below formula looks to me as though it would accurately
count the number of times whatever's in column B appears in the specified
range of the menu-day sheets. You don't need to say SUM, since you're using
plus signs between each sheet, but it shouldn't hurt anything.

If you want to simplify a bit - I tend to like to put intermediate results
in helper columns - you could set up seven helper columns, say O:U, in the
Grocery List sheet; each would be headed (in say row 1) with the day of the
week, and O5 in this range would say =COUNTIF(INDIRECT(O$1&"!B$20:C$86"),B5);
this could be copied to every other cell, and each cell would then tell how
many times the menu item of this row appeared on the menu of each day of the
week. Then back in your column you could do =SUM(O5:U5) to count for the
whole week.

I don't see where you're handling number of servings, though. And I may
have completely misunderstood what's in all those sheets, because after all
you said it WASN'T counting right. So maybe you should straighten me out on
that.

--- "scottia" wrote:
Using the following formula to count text values selected from a drop list
(menu day). Once selected (menu list), value from "menu day" is added to a
different sheet (Grocery List). In the separate sheet (Grocery List) I am
trying to increase "servings" based on foods consumed in "menu list". I
can't get the "serving" values to increase in "Grocery List" when a "serving"
is greater than 1 in "menu list".

=SUM(COUNTIF(Sunday!B$20:C$86,B185)+COUNTIF(Monday !B$20:C$86,B185)+COUNTIF(Tuesday!B$20:C$86,B185)+C OUNTIF(Wednesday!B$20:C$86,B185)+COUNTIF(Thursday! B$20:C$86,B185)+COUNTIF(Friday!B$20:C$86,B185)+COU NTIF(Saturday!B$20:C$86,B185))

Above formula works for counting all foods consumed from "menu list" by
adding them to "Grocery List" but additional servings cannot be added to
quantities consumed calculated in "Grocery List".

I am trying to figure out how to count multiple unique values with servings
(a separate column) in "Grocery List" once a food is counted from "Menu Items"