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

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

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

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

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 -