Conditional Sums
Conditional Sums
(OP)
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.
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.





RE: Conditional Sums
thanks.
RE: Conditional Sums
RE: Conditional Sums
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$20:D60)]
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.
RE: Conditional Sums
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Conditional Sums
Then, instead of SUM, use SUBTOTAL funcition... =Subtotal(9,D20:D60). 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.