Conditional Sums 1

[jartgo]

In a pay request spreadsheet, I have different categories of invoices (i.e., Construction, Engineering, Inspection, etc.) So, I have two columns of data, "Category" and "Amount"

I want to sum the amounts in each category, how? Everything I've seen only lets you based the condition on a number not text.

Essentially, what I want to do is, "sum all D cells in this range if the corresponding C cells are 'construction'" and likewise for the other categories, so that I have a running total in one location.

 

[jartgo]

got it figured out.
thanks.

 

[MintJulep]

Please post your solution so that others may benefit.

 

[jartgo]

well I really wouldn't call it a "solution" more of a work-around.

At the top of my spreadsheet I have a column of categories (i.e., construction, engineering, inspection, admin). Below that I have a list of all invoices with columns for date, vendor, category, and amount. In the "category" cell for each invoice, I created a drop down list, that references the list of categories at the top of the spreadsheet, so that you can only enter one of those four allowable categories.

Back up at the top, I wanted to sum the invoices from each category next to the category label. What I ended up doing was [=SUMIF($C$20:C60,"Construction",$D$2060)]

This essentially says to sum all cells from D20 to D60 where the corresponding cell in the "C" column says "Construction"
I'd love to just reference the cell where I entered the allowable categories rather than retyping the category type, but didn't get that figured out. If anyone has any suggestions, I love to hear them.

 

[joerd]

You could, instead of the "Construction" part, reference the cell that has the word Construction in it (e.g. $A$2)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[dgsbsme]

If you are using Microsoft Excel (possibly other spreadsheet software as well) you could use the "AutoFilter" and filter for whatever Category you want. Even combinations of Categories, using the "Custom" option once the filter is on.

Then, instead of SUM, use SUBTOTAL funcition... =Subtotal(9,D2060). The Function Number 9 is SUM option of the Subtotal function. There are also options for Average, or Count, etc (look up SUBTOTAL) in the MS Assistant).

Anyway, the benefit of the SUBTOTAL function is that it only evaluates the range that passes through the filter. No need to edit a formula to change to a different category.