Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
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?
Comments
-
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.
-
@ 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
-
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.
-
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.
-
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!
-
I believe you can unhide a single column or every column - not groups
