×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Conditional Sums

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.

RE: Conditional Sums

(OP)
got it figured out.
thanks.

RE: Conditional Sums

Please post your solution so that others may benefit.

RE: Conditional Sums

(OP)
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$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

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Conditional Sums

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,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.   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources