SUMIFS trouble

TJ-Webfoot

I feel like I'm almost there with this, but just can't figure it out. I'm trying to determine a Sales Rate (money/hours) per Estimator, per month (date range).

My column headings are: Ops Start Date, Estimator, Scope Hr, and Contract Amount.

I think the problem is using a date range within a SUMIFS formula. I know its possible within excel just like I have it below, but not within SS. Does anyone have a suggested workaround?

 

=SUMIFS(Contract Amount:Contract Amount, Ops Start Date:Ops Start Date, ">="&DATE(2018,1,1),Ops Start Date:Ops Start Date, "<"&DATE(2018,1,31), Estimator:Estimator, "Nathan")

Mike Wilday

Yeah, it does looks like your date items are an issue in there. Try removing the & signs and quotations. Also, Column names with spaces should be surrounded by brackets like this: [Contract Amount]:[Contract Amount]

Another thing I've seen done successfully is adding separate sum-if statements together. =sumif(Range, Criterion)+sumif(Range, Criterion)+sumif(Range Criterion) will give you the same results but is a longer formula. 

Test each sum-if by itself to ensure you get results. Then you can concatenate them with the + sign. Hope that Helps! 

TJ-Webfoot

I figured it out. Took some monumental effort and some SUPERB help from Lex on the support team. Basically "SUMIFS" don't work with date ranges. The way around it was to add a column and formula using an IF statement in conjuction with the MONTH syntax; it looked at the date (1/1/2018 format) and turned it into the Month. And in order to avoid the "#Invalid Data Type" error, (this took A LOT of troubleshooting) you have to put the ISBLANK syntax at the beginning of the formula and put in quotes for the false statement, but just in the first nested variable... (don't forget to ensure you have enough parenthesis at the end!)

=IF(ISBLANK([Ops Start Date]162), " ", IF(MONTH([Ops Start Date]162) = 1, "January", IF(MONTH([Ops Start Date]162) = 2, "February", IF(MONTH([Ops Start Date]162) = 3, "March", IF(MONTH([Ops Start Date]162) = 4, "April", IF(MONTH([Ops Start Date]162) = 5, "May", IF(MONTH([Ops Start Date]162) = 6, "June", IF(MONTH([Ops Start Date]162) = 7, "July", IF(MONTH([Ops Start Date]162) = 8, "August", IF(MONTH([Ops Start Date]162) = 9, "September", IF(MONTH([Ops Start Date]162) = 10, "October", IF(MONTH([Ops Start Date]162) = 11, "November", IF(MONTH([Ops Start Date]162) = 12, "December")))))))))))))

 

 

TJ-Webfoot

Once I had the date issue figured out, I was able to get total dollars (contract amount) by using the formula:

=SUMIFS([Contract Amount]:[Contract Amount], Month:Month, "January", Estimator:Estimator, "Nathan")

 

To get the hours:

=SUMIFS([Scope Hr]:[Scope Hr], Month:Month, "January", Estimator:Estimator, "Nathan")

 

Then it's a simple formula in another column to divide the resulting hours into the dollars to get the sales rate for the month for a specific estimator.

Now I just need to extrapolate this across all 12 months times 5 estimators. Yay!

Mike Wilday

Hey, thanks for sharing the solution.

A special trick I learned regarding closing brackets... if you leave off all of the final closing parenthesis. Smartsheet will close them all for you.

Robert S.

Hello TJ,

 

Thanks for the question. It sounds like you were able to find a solution that might work for you, however I wanted to clear up a few things I've noticed while reading through this thread. Mike is correct about column names with spaces needing square brackets around them [ ], just to note however, they're also needed for column names that contain numbers or special characters. More on references can be found here (https://help.smartsheet.com/articles/2476171#reference).

 

The bigger thing I wanted to note, is that SUMIFS formulas can work with date ranges and your original formula was very close in how to do it. Here's an example of how this formula would look:

 

=SUMIFS([Contract Amount]:[Contract Amount], [Ops Start Date]:[Ops Start Date], >=DATE(2018, 1, 1), [Ops Start Date]:[Ops Start Date], <=DATE(2018, 1, 31), Estimator:Estimator, "Nathan")

 

You can use this formula for the Scope Hr column as well by replacing [Contract Amount]:[Contract Amount] with [Scope Hr]:[Scope Hr]. If you want each of these results visible in their own cells, and then have the resulting dollars divided by the resulting hours in another cell, you can do so as you said in your last post. If you'd prefer to have this all done in one cell instead, you can combine these formulas into one similar to this:

 

=SUMIFS([Contract Amount]:[Contract Amount], [Ops Start Date]:[Ops Start Date], >=DATE(2018, 1, 1), [Ops Start Date]:[Ops Start Date], <=DATE(2018, 1, 31), Estimator:Estimator, "Nathan") / SUMIFS([Scope Hr]:[Scope Hr], [Ops Start Date]:[Ops Start Date], >=DATE(2018, 1, 1), [Ops Start Date]:[Ops Start Date], <=DATE(2018, 1, 31), Estimator:Estimator, "Nathan")

 

The last thing I wanted to note was about Mike's adding separate sum-if statements together workaround. This is going to result in a different result than what you're looking for. This option works more as an OR statement, and it seems like you're looking for an AND statement since you only want to sum rows that match all of the criteria. This option is great if you wanted the formula to sum row the match any of the criteria rather than all of the criteria.

Mike Wilday

Thanks for those points of clarification Robert.

Stephanie Stoewe

Will this formula work for {Reference Sheets}?