I have a spreadsheet with 3 types of rows -- hardware, software, and support.
Column A in each row starts with HW-xxxx-yyyyy or SW-xxxx-yyyy or
SP-xxxx-yyyyy. Column G has the price for that row/part. The number of
rows will vary based upon project but will typically be less than 100. At
the bottom I want 3 subtotals -- one for hardware, one for software, and one
for support. The hw, sw and support parts per system component are clearly
grouped so if I sort on Column A and do subtotals I lose clarity of the
information being presented. I don't want to rearrange the rows in order to
calculate the subtotals and I don't want to have to manually add all of the
correct rows for each subtotal for every project. I want a subtotal that
does something like the following

For HW subtotal -- For x=1..75 (sum(if cell ax='hw-', then include value
from cell gx in sum, otherwise include zero in sum))

The 1..75 input parameters in the formula for that cell would be customized
by project, but otherwise the formula will work consistently for every
project without further modification.

Is there a way to do this with standard Excel formulas? Can this be done
with a macro? Any other ideas?

I'm not sure what 1..75 represents. If your data is in rows 1-75, this
formula should give you the hardware subtotal:

=SUMPRODUCT(--(LEFT($A$1:$A$75,3)="HW-"),$G$1:$G$75)

the same formula should also work for software and support subtotals, bu
substituting SW- or SP- for HW-.

Bob Phillips explains =sumproduct() in more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

"Duke Bond" wrote:

I have a spreadsheet with 3 types of rows -- hardware, software, and support.
Column A in each row starts with HW-xxxx-yyyyy or SW-xxxx-yyyy or
SP-xxxx-yyyyy. Column G has the price for that row/part. The number of
rows will vary based upon project but will typically be less than 100. At
the bottom I want 3 subtotals -- one for hardware, one for software, and one
for support. The hw, sw and support parts per system component are clearly
grouped so if I sort on Column A and do subtotals I lose clarity of the
information being presented. I don't want to rearrange the rows in order to
calculate the subtotals and I don't want to have to manually add all of the
correct rows for each subtotal for every project. I want a subtotal that
does something like the following

For HW subtotal -- For x=1..75 (sum(if cell ax='hw-', then include value
from cell gx in sum, otherwise include zero in sum))

The 1..75 input parameters in the formula for that cell would be customized
by project, but otherwise the formula will work consistently for every
project without further modification.

Is there a way to do this with standard Excel formulas? Can this be done
with a macro? Any other ideas?