I used
=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1: 7")),"dddd")&"!B20:C86"),B185))
but realized I needed to account for a sheet named "ExtraFoodList". The end
result was :

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1: 7")),"dddd")&"!B20:C86"),B11))+COUNTIF(ExtraFoodLi st!B$3:B$900,B11)

Anyway to consolidate this one?



"T. Valko" wrote:

Not sure what you're doing but you can replace this:

=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))

With this:

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1: 7")),"dddd")&"!B20:C86"),B185))

If you will *never* insert new rows above row 8 we can make it even shorter:

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(1:7),"dddd") &"!B20:C86"),B185))

--
Biff
Microsoft Excel MVP


"scottia" wrote in message
...
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 separte column) in "Grocery List" once a food is counted from "Menu
Items"