Sumif for date ranges

This did not seem to be covered and I'm hoping to find a formula without adding extra columns to my sheet.

Curently I have a secondary column separately maintained which lists months and I Sumif by this secondary "month" column. I would like to Sumif based on date ranges. Not simply from today() but actual Date A - Date B and Date B - Date C and so on... Does smartsheet support such Sumif formulas?

Find more posts tagged with

Formulas

Comments

SashaR

I think you will need to add additional columns to identify if the corresponding date cell is within a range, then use a SUMIFS based on the checkbox columns. SUMIFS cant look at a range of two dates.

Peter Kirkham

@ Thane: If you don't want to add the additional column to your sheet because you are tying to avoid adding visual clutter to the sheet, then don't forget that you can also hide the column. I do this with many 'utility' columns that I add to my sheets in order to get various formulae to work.

Peter

Thane Knutson

Peter: I can hide columns but since smartsheet does not support unhiding just one column anyone who wants to see thier hidden columns must show the entire sheet then you must re-hide columns individually. Also, besides SUMIFS it needs to be visible so people remember to update the column if the date column changes months.

SashaR: I'm not even sure how you justify specific dates with the formulas. Seems you can only compare two cells against each other for date ranges? I want to have my MONTH column formula to say something like *If START 1/1/16-1/31/16 then January, etc* and nest this formula for all the months.

SashaR

Thane, you can unhide individual columns. Click and drag where the column is hidden to unhide it.

If you are always using full months as a range, use the MONTH() function in a nested IF.

Here's a start:

=IF(MONTH(Start3) = 1, "January", IF(MONTH(Start3) = 2, "February"))

Do this for each month then use SUMIF to sum for each month or a range.

Screen Shot 2016-02-17 at 9.32.56 AM.png

Thane Knutson

Excellent! This is excatly the formula I needed.

Can you unhide groups of columns? The left side of our sheet has hidden columns for one department and the right side has hidden columns for another department.

Thanks for your help!