SUMIF with date range and dropdown list categories

Anastasia · 09/28/18

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

J. Craig Williams · 09/29/18

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 Newcome · 10/02/18

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, Type@row, 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()))

Anastasia · 10/03/18

Thank you Craig! Will be experimenting

Anastasia · 10/03/18

Thank you!

Anastasia · 10/03/18

It worked, thank you so much!!

We'll try referencing afterwards too

SUMIF with date range and dropdown list categories

Comments

Categories