| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add adjacent cell value to existing CountIF formula
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" |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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$8 6=B183)+(Thursday!$B$20:$B$86=B183)+(Friday!$B$20: $B$86=B183)+(Saturday!$B$20:$B$86=B183)) "Bob Bridges" wrote: 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" |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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" |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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. "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" |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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$8 6, 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$8 6,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" wrote: 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" |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
Thanks for solving the 2nd half of my problem. Your formula worked perfectly.
"Bob Bridges" wrote: 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)) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
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" |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add adjacent cell value to existing CountIF formula
Anyway to consolidate this one?
Not really. What you have is about the best that can be done. -- Biff Microsoft Excel MVP "scottia" wrote in message ... 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" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for max number and the data adjacent to the cell | Excel Discussion (Misc queries) | |||
| Using CountIf with non-adjacent cells in same column | Excel Discussion (Misc queries) | |||
| Countif across multiple non-adjacent cells | Excel Discussion (Misc queries) | |||
| existing unique value formula help (index(match(countif))) | Excel Worksheet Functions | |||
| How do I use countif across non-adjacent cells? | Excel Worksheet Functions | |||
Linear Mode
