Add adjacent cell value to existing CountIF formula

[scottia]

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)+COUNTIF(Wednesday!B$20:C$86,B185)+COUNTIF(Thursday!B$20:C$86,B185)+COUNTIF(Friday!B$20:C$86,B185)+COUNTIF(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"

 

[Bob Bridges]

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]

Hopefully this will clarify my problem. Below are the sheets used.

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$86=B183)+(Thursday!$B$20:$B$86=B183)+(Friday!$B$20:$B$86=B183)+(Saturday!$B$20:$B$86=B183))

 

[T. Valko]

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)+COUNTIF(Wednesday!B$20:C$86,B185)+COUNTIF(Thursday!B$20:C$86,B185)+COUNTIF(Friday!B$20:C$86,B185)+COUNTIF(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))

 

[scottia]

Thanks. I used
=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT("1:7")),"dddd")&"!B20:C86"),B185)) since the sheet is continually evolving.

I am still trying to figure out how to SUM values from other sheets into
this one to get an accurate serving count (See prior post for details).

Thanks for cleaning this formula up.

 

[scottia]

Below is the formula I am using to count individual servings used from
multiple sheets (Days). It works to count individual foods on a day once but
won't count multiple instances of the same food for the same day (Same
Sheet). Please review

=INDEX(Sunday!B$20:C$86,MATCH(B183,Sunday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Sunday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Sunday!C$20:C$86))+INDEX(Monday!B$20:C$86,MATCH(B183,Monday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Monday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Monday!C$20:C$86))+INDEX(Tuesday!B$20:C$86,MATCH(B183,Tuesday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Tuesday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Tuesday!C$20:C$86))+INDEX(Wednesday!B$20:C$86,MATCH(B183,Wednesday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Wednesday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Wednesday!C$20:C$86))+INDEX(Thursday!B$20:C$86,MATCH(B183,Thursday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Thursday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Thursday!C$20:C$86))+INDEX(Friday!B$20:C$86,MATCH(B183,Friday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Friday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Friday!C$20:C$86))+INDEX(Saturday!B$20:C$86,MATCH(B183,Saturday!B$20:B$86,
0),2)-SUMPRODUCT(--ISNUMBER(Saturday!C$20:C$86))+SUMPRODUCT(--ISNUMBER(Saturday!C$20:C$86))

 

[Bob Bridges]

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]

Thanks for solving the 2nd half of my problem. Your formula worked perfectly.

 

[scottia]

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(ExtraFoodList!B$3:B$900,B11)

Anyway to consolidate this one?

 

[T. Valko]

Anyway to consolidate this one?

Not really. What you have is about the best that can be done.