Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing/Counting Data in One Column Based on Criteria Another Col
This level of Excel complexity is a little above my skills, and am hoping
that someone can help. I have numbered data in one range of cells (e.g., b1:b158) and alpha criteria in another (e.g., "apples", "oranges", "pears" in c1:c158). I am trying to sum the data in cells b1:b158 based on the criteria in cells c1:c158, and put the result in a third cell. So for example, the sum of the numbers in column b would be put in cell d1 if the criteria in c1:c158 was equal to "apples"; the sum of the numbers in b would be put in d2 if the criteria in c1:c158 was "oranges", etc. Anyone have any suggestions? Thanks in advance for your help. -- agilek9 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing/Counting Data in One Column Based on Criteria Another Col
In F1, F2,... enter the 'Fruit' names...
Enter this in D1 =Sumproduct(--(C$1:C$158=F1),(B$1:B$158)) and copy down... If F1 is Apple then it will give you the sum for the rows which have Apple in Col C... "agilek9" wrote: This level of Excel complexity is a little above my skills, and am hoping that someone can help. I have numbered data in one range of cells (e.g., b1:b158) and alpha criteria in another (e.g., "apples", "oranges", "pears" in c1:c158). I am trying to sum the data in cells b1:b158 based on the criteria in cells c1:c158, and put the result in a third cell. So for example, the sum of the numbers in column b would be put in cell d1 if the criteria in c1:c158 was equal to "apples"; the sum of the numbers in b would be put in d2 if the criteria in c1:c158 was "oranges", etc. Anyone have any suggestions? Thanks in advance for your help. -- agilek9 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing/Counting Data in One Column Based on Criteria Another Col
On Nov 6, 3:56*pm, agilek9 wrote:
This level of Excel complexity is a little above my skills, and am hoping that someone can help. I have numbered data in one range of cells (e.g., b1:b158) and alpha criteria in another (e.g., "apples", "oranges", "pears" in c1:c158). *I am trying to sum the data in cells b1:b158 based on the criteria in cells c1:c158, and put the result in a third cell. *So for example, the sum of the numbers in column b would be put in cell d1 if the criteria in c1:c158 was equal to "apples"; the sum of the numbers in b would be put in d2 if the criteria in c1:c158 was "oranges", etc. Anyone have any suggestions? *Thanks in advance for your help. -- agilek9 SUMIF and COUNTIF. =SUMIF(C1:C158,"apples",B1:B158) =COUNTIF(C1:C158,"apples") But it would be better to put each item in another cell and reference them. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting items in one column based on criteria in another | Excel Worksheet Functions | |||
summing column c based an column a criteria | Excel Worksheet Functions | |||
summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
Summing data based on criteria | Excel Discussion (Misc queries) | |||
Counting & Summing based on criteria on another column | Excel Discussion (Misc queries) |