SUMIFS Question

Jaclyn Royer

Could you please provide guidance on the "SUMIFS" formula? I'm trying to calculate the following information:

1. Sum the amount of a service type completed between a certain amount of time based on the price.
2. Goal to add the amount of Nutrition Facts Revision service types completed between April 1 and April 30th based on the Invoice date. 
3. Columns that using Service Type (Drop Down Text), Invoice Date (Calendar Dates), and Price (Dollars) 
4. I tried =SUMIFS(Price:Price, [Invoice Date]:[Invoice Date], >=4 / 1 / 19, [Invoice Date]:[Invoice Date] <= 4 / 30 / 19, [Service Type]:[Service Type] = "Nutrition Facts Revision") but it computes to #Invalid Operation.

Thanks!

David Joyeuse

Hi @Jaclyn Royer

I did not separate ranges & criteria correctly, also, and if this formula is suppose to calculate the total price for April 19 for the specified service then I would suggest you to use this instead:

=SUMIFS([Price]:[Price],[Invoice Date]:[Invoice Date], AND(MONTH(@cell)=4, YEAR(@cell)=2019), [Service Type]:[Service Type], "Nutrition Facts Revision")

This will do it, and you just need to change the number of the month or year if you want to decline this formula to every month of the year, or future years.

Hope it helped!

Jaclyn Royer

Thank you!!

Paul Newcome

As a note...

The reason the original formula didn't work is because of how you are referencing the date and because of not separating the range/criteria. To use a date in a formula you have to use the DATE function.

DATE(yyyy, mm, dd)

=SUMIFS(Price:Price, [Invoice Date]:[Invoice Date], >= DATE(2019, 04, 01), [Invoice Date]:[Invoice Date], <= DATE(2019, 04, 30), [Service Type]:[Service Type], = "Nutrition Facts Revision")

Having said that... I personally find it much easier to manage using David's solution.

First: just updating month and year is much easier than trying to remember which months have how many days.

Second: I prefer to use the AND function to join criteria for the same range together. I find that it helps keep things organized in long/complex formulas. It also cuts down on keystrokes and makes it easier for me to read. It also helps cut down on the chances of accidentally grabbing the wrong date column for the month vs the year. Sometimes I move a little too quickly especially with cross sheet references. Haha. That is always a frustrating issue to clear up.

Jaclyn Royer

Thank you Paul!

Paul Newcome

Happy to help. 👍️