sum of cells based on word

[gururajv]

Hi,

I have 2 columns,

I II

ca 2.4
mca 5.5
ca 2.2
mca 6.0
sc 9.0
mca 5.5
ca 3.4
sc 9.5


I need to add cells in column II based on col I data.,
for e.g., add all cells when col I has "ca" and so on..

pls someone help me doing this.....

thnks.

 

[ivano]

Hi,

I have 2 columns,

I II

ca 2.4
mca 5.5
ca 2.2
mca 6.0
sc 9.0
mca 5.5
ca 3.4
sc 9.5


I need to add cells in column II based on col I data.,
for e.g., add all cells when col I has "ca" and so on..

pls someone help me doing this.....

thnks.

=SUMIF($A$1:$A$10,"*ca*",$B$1:$B$10)
ciao ivano

 

[Harald Staff]

Hi

A Pivot table will do this for you. See
http://www.cpearson.com/excel/pivots.htm

or you could set it up with a SUMIF formula for each word. But that's far
more work.

 

[Gary Thomson]

Assuming the following:

Column A Column B
Row 1 ca 2.4
Row 2 mca 5.5
Row 3 ca 2.2
Row 4 mca 6.0
Row 5 ca 9.0
Row 6 sc 5.5

then the following will work:

=sumif(A1:A6,"ca",B1:B6) will return (2.4+2.2+9.0)=13.6.
=sumif(A1:A6,"mca",B1:B6) will return (5.5+6.0)=11.5.
=sumif(A1:A6,"sc",B1:B6) will return 5.5.