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 |
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 |
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. |
All times are GMT +1. The time now is 08:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com