Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count values inside a cell when refered elsewhere?
Hi, Hope you can help..... A1 - A10 contains: A,A,B,C,B,B,C,A,A,C B1 - B10 contains: 10,5,10,3,5,5,10,7,5,10 How can I add up the contents of the cells that relate to 'A' i.e. answer should be 27 (10+5+7+5) I've tried - SUMIF, IF(SUM), SUM(COUNTIF) and I still can't get it to look at A1-A10 and then add up those in B1-B10 that relate. Thanx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count values inside a cell when refered elsewhere?
Have you tried this:
=SUMIF(A1:A10,"A",B1:B10) ? Better still, put "A" in another cell, eg C1, and put this formula in D1: =SUMIF(A1:A10,C1,B1:B10) Now you can just change the entry in C1 to get the sum for other letters. If you get zero as the result, then your "numbers" in column B are probably text values that look like numbers. Hope this helps. Pete On Aug 21, 11:44 am, littlejess22 wrote: Hi, Hope you can help..... A1 - A10 contains: A,A,B,C,B,B,C,A,A,C B1 - B10 contains: 10,5,10,3,5,5,10,7,5,10 How can I add up the contents of the cells that relate to 'A' i.e. answer should be 27 (10+5+7+5) I've tried - SUMIF, IF(SUM), SUM(COUNTIF) and I still can't get it to look at A1-A10 and then add up those in B1-B10 that relate. Thanx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count values inside a cell when refered elsewhere?
One way
=SUMPRODUCT((A1:J1="a")*(A2:J2)) Mike "littlejess22" wrote: Hi, Hope you can help..... A1 - A10 contains: A,A,B,C,B,B,C,A,A,C B1 - B10 contains: 10,5,10,3,5,5,10,7,5,10 How can I add up the contents of the cells that relate to 'A' i.e. answer should be 27 (10+5+7+5) I've tried - SUMIF, IF(SUM), SUM(COUNTIF) and I still can't get it to look at A1-A10 and then add up those in B1-B10 that relate. Thanx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count values inside a cell when refered elsewhere?
Pete, Thank you so much....perferct. Don't know why I didn't try that!
Thanks Jess "Pete_UK" wrote: Have you tried this: =SUMIF(A1:A10,"A",B1:B10) ? Better still, put "A" in another cell, eg C1, and put this formula in D1: =SUMIF(A1:A10,C1,B1:B10) Now you can just change the entry in C1 to get the sum for other letters. If you get zero as the result, then your "numbers" in column B are probably text values that look like numbers. Hope this helps. Pete On Aug 21, 11:44 am, littlejess22 wrote: Hi, Hope you can help..... A1 - A10 contains: A,A,B,C,B,B,C,A,A,C B1 - B10 contains: 10,5,10,3,5,5,10,7,5,10 How can I add up the contents of the cells that relate to 'A' i.e. answer should be 27 (10+5+7+5) I've tried - SUMIF, IF(SUM), SUM(COUNTIF) and I still can't get it to look at A1-A10 and then add up those in B1-B10 that relate. Thanx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Count values inside a cell when refered elsewhere?
Thanks for feeding back, Jess - glad to be of help.
Pete On Aug 21, 1:32 pm, littlejess22 wrote: Pete, Thank you so much....perferct. Don't know why I didn't try that! Thanks Jess "Pete_UK" wrote: Have you tried this: =SUMIF(A1:A10,"A",B1:B10) ? Better still, put "A" in another cell, eg C1, and put this formula in D1: =SUMIF(A1:A10,C1,B1:B10) Now you can just change the entry in C1 to get the sum for other letters. If you get zero as the result, then your "numbers" in column B are probably text values that look like numbers. Hope this helps. Pete On Aug 21, 11:44 am, littlejess22 wrote: Hi, Hope you can help..... A1 - A10 contains: A,A,B,C,B,B,C,A,A,C B1 - B10 contains: 10,5,10,3,5,5,10,7,5,10 How can I add up the contents of the cells that relate to 'A' i.e. answer should be 27 (10+5+7+5) I've tried - SUMIF, IF(SUM), SUM(COUNTIF) and I still can't get it to look at A1-A10 and then add up those in B1-B10 that relate. Thanx- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table Lookup formula where 2 known values are inside array | New Users to Excel | |||
How to count the number of 'values' in a cell | Excel Worksheet Functions | |||
Count values in a cell | Excel Worksheet Functions | |||
Count number of values in a cell | Excel Worksheet Functions | |||
Count values bigger than cell | Excel Discussion (Misc queries) |