SUMIF with date range and dropdown list categories

edited 12/09/19 in Using Smartsheet
09/28/18 Edited 12/09/19

Hi everyone,

So I have columns Type (dropdown list, 3 values, Tools, Chemicals, Clothing), Date and Amount ($).

 I am trying to come up with a formula to sum amount of $ spent in a certain month in each Type of expense. So, for example, for much was spent in type Clothing between 06/01/18 and 06/30/18.

Is there a way to do it? We have other sheets regarding our budget where there are as many as 4-6 values for dropdown Type column. 

Please help

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Assumption: 'between 06/01/18 and 06/30/18' implies Month = June, Year = 2018 and you aren't (yet) looking for a generic 'enter two dates - determine sum for them' (can be done, not so simple)

    Assumption: this formula is not in the [Type], [Date], or [Amount ($)] columns. 

    Basics:

    =SUMIFS([Amount ($)]:[Amount ($)], Type:Type, "Tools", Date:Date, AND(MONTH(@cell) = 6, YEAR(@cell) = 2018))

    That will sum the amounts where the Type is "Tools" and the Date is June 2018 and it will do so over the entire column.

    You could copy that somewhere and then manually change the criteria. I don't recommend it. (3 choices x 12 months x Y years ...)

    Advanced:

    Ultimately, I would transfer the formulas to another Sheet and reference them using X-Sheet References. 

    https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

    You can replace "Tools", 6, and 2018 with references to cells that hold the values. 

    I hope this should get you started

    Craig

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    A few variations...

     

    If you have a simple table set up where each dropdown selection is listed only once (possibly at the top of the page as a summary or on another sheet using x-sheet references), you could use

    =SUMIFS([Amount ($)]:[Amount ($)], Type:Type, [email protected], Date:Date, AND(MONTH(@cell) = 6, YEAR(@cell) = 2018))

     

    That will give you the total for whatever is in the Type column for the row your formula is in.

     

    This will give you a running total of the current month:

     

    =SUMIFS([Amount ($)]:[Amount ($)], Type:Type, "Tools", Date:Date, AND(MONTH(@cell) = TODAY(), YEAR(@cell) = TODAY()))

  • Thank you Craig! Will be experimenting 

  • Thank you!

  • It worked, thank you so much!! 

    We'll try referencing afterwards too

Sign In or Register to comment.