Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
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 datasubtotals, 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 Alpruett wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
Check out the help info for SumIf, you could also try Data-Subtotals and see
if that works for you. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Alpruett" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
Alpruett wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
These suggestions have been what I need, except I can't seem to drag the
formula through the thousands of rows that I have without having the range and sum_range change. I want the criteria to change, but the ranges to stay the same. Is this possible? I've tried setting the pattern for 5 rows that only the criteria changes, but it still won't pick up on that. Thanks for the help. -Alison "Peo Sjoblom" wrote: 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 datasubtotals, 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 Alpruett wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
Use absolute references
$A$2:$A$200 will not change if copied down Peo Alpruett wrote: These suggestions have been what I need, except I can't seem to drag the formula through the thousands of rows that I have without having the range and sum_range change. I want the criteria to change, but the ranges to stay the same. Is this possible? I've tried setting the pattern for 5 rows that only the criteria changes, but it still won't pick up on that. Thanks for the help. -Alison "Peo Sjoblom" wrote: 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 datasubtotals, 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 Alpruett wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can IF be used to sum?
Anchor the ranges.
=SUMIF($A$2:$A$100,C2,$B$2:$B$100) Gord Dibben MS Excel MVP On Wed, 27 Dec 2006 14:05:02 -0800, Alpruett wrote: These suggestions have been what I need, except I can't seem to drag the formula through the thousands of rows that I have without having the range and sum_range change. I want the criteria to change, but the ranges to stay the same. Is this possible? I've tried setting the pattern for 5 rows that only the criteria changes, but it still won't pick up on that. Thanks for the help. -Alison "Peo Sjoblom" wrote: 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 datasubtotals, 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 Alpruett wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|