Can IF be used to sum?

[Guest]

I have a table that was a dbf in a GIS. I have one column that is the name
of a soil type and another column is the area that the soil occupies. The
problem is, I have a bunch of rows for each soil type. I want to figure out
the sum total of the area that each soil type occupies without having to
manually sum each group. Some of the soil types have upwards of 20 rows that
need to be added together and others are only 1 row. So, is there a way that
an IF statement (or something else) can add up those areas that have the same
"soil type"? Or am I expecting too much? Any advice is much appreciated.
Thanks.

 

[Peo Sjoblom]

Sounds like you could use pivot tables for this, instructions here

http://peltiertech.com/Excel/Pivots/pivotstart.htm

another way would be to sort the table and use data>subtotals, then
select for each change in soil type

finally you could use

=SUMIF(A2:A100,"soil type",B2:B100)

where A2:A100 holds the soil type and B2:B100 the area

replace soil type with the de facto name or with a cell like

=SUMIF(A2:A100,C2,B2:B100)

and put the soil type in the cell


Regards,

Peo Sjoblom

www.nwexcelsolutions.com

 

[Manju]

I have a table that was a dbf in a GIS. I have one column that is the name
of a soil type and another column is the area that the soil occupies. The
problem is, I have a bunch of rows for each soil type. I want to figure out
the sum total of the area that each soil type occupies without having to
manually sum each group. Some of the soil types have upwards of 20 rows that
need to be added together and others are only 1 row. So, is there a way that
an IF statement (or something else) can add up those areas that have the same
"soil type"? Or am I expecting too much? Any advice is much appreciated.
Thanks.

Try using SUMIF function. Use excel help for the function it has got an
example also.

Regards.

 

[Peo Sjoblom]

Use absolute references

$A$2:$A$200

will not change if copied down



Peo

 

[Gord Dibben]

Anchor the ranges.

=SUMIF($A$2:$A$100,C2,$B$2:$B$100)


Gord Dibben MS Excel MVP