SUMIF with multiple dates across columns without duplicating
Need a formula that can sum the [Incremental Sales Forecast] column if the columns [Initial Sales Presentation]:[Verbal Agreement] has a date in any cell and without duplicating if several cells have dates.
=SUMIFS({Reference Range (Incremental Sales Forecast)}, {Reference Range ([Initial Presentation]:[Verbal Agreement])},ISDATE(@cell)
Above formula doesn't work, does if the second range is only one column, need it to read across all four.
Any suggestions? @Andrée Starå @Paul Newcome
Likely simple to fix, and just not seeing it.
Best Answers
-
I would create a checkmark on that sheet which determines if each cell is a date and make the checkmark a requirement for Sumifs.
=IF(And(ISDATE([Initial Presentation]), ISDATE(Survey), ISDATE([Final Presentation]), ISDATE([Verbal Agreement])),1,0)
You could put the following in your checkbox column, hide it, and then use that column in your sumifs statement.
Would that work?
-
All ranges must be of the same size and shape. You cannot sum a single column range based on data within a four column range.
-
Maybe like this... I was just trying to break down his original formula from
=SUMIFS({Reference Range (Incremental Sales Forecast)}, {Reference Range ([Initial Presentation]:[Verbal Agreement])},ISDATE(@cell)
To: =SUMIFS({Reference Range (Incremental Sales Forecast)}, {Reference Range (Checkbox Column)},=1)
Answers
-
Tried this and only produces #INCORRECT ARGUMENT
=SUMIFS({COPY COPY KAE Call Report Template (Test M Range 1}, MIN({COPY COPY KAE Call Report Template (Test M Range 5}, MAX({COPY COPY KAE Call Report Template (Test M Range 9}, ISDATE(@cell))))
Range 1 = Incremental Sales
Range 5 = Initial Presentation
Range 9 = Verbal Agreement
-
I would create a checkmark on that sheet which determines if each cell is a date and make the checkmark a requirement for Sumifs.
=IF(And(ISDATE([Initial Presentation]), ISDATE(Survey), ISDATE([Final Presentation]), ISDATE([Verbal Agreement])),1,0)
You could put the following in your checkbox column, hide it, and then use that column in your sumifs statement.
Would that work?
-
All ranges must be of the same size and shape. You cannot sum a single column range based on data within a four column range.
-
@Mike Wilday Thank you. Used the formula as suggested and it is checking boxes when no date is present in the columns and no check when all have dates? Any ideas? Basically just need it to sum the Incremental Sales Column for anything with a date in the specified columns and to stay blank if a date is in the Qualified win column with out duplicating. In a perfect world also would have it recognize the same range based on the current month and year with the ISDATE(MONTH(@cell)=11 or MONTH(TODAY())
@Paul Newcome other suggestion on how to accomplish?
-
That's super peculiar. Try swapping the 1 and 0. Does that work?
-
I would try swapping the AND with an OR.
As for the duplicate dates... I'm not sure I follow.
-
@Paul He wants to make sure that all the dates are entered before it checks the box, then he is summing all the values of those that have all the dates. Using the checkbox. OR would cause it to check if any of them were dates.
-
Ah. I misread that. Ok. So all 4 columns must contain a date and the 5ht column (Qualified Win) must be blank) to sum the row.
Would it work if we checked for the above and then just pulled the number value into the column and summed the entire column instead of checking a box and then summing a separate column based on that box?
=IF(AND(ISDATE([Initial Presentation]@row), ISDATE(Survey@row), ISDATE([Final Presentation]@row), ISDATE([Verbal Agreement]@row), ISBLANK([Qualified Win]@row)), [Incremental Sales Forecast]@row, 0)
-
Maybe like this... I was just trying to break down his original formula from
=SUMIFS({Reference Range (Incremental Sales Forecast)}, {Reference Range ([Initial Presentation]:[Verbal Agreement])},ISDATE(@cell)
To: =SUMIFS({Reference Range (Incremental Sales Forecast)}, {Reference Range (Checkbox Column)},=1)
-
Yes. That works perfectly fine.
I wasn't trying to say you were wrong. I just personally try to keep the end formulas as simple as possible and build the complexity into the helper columns. Since my helper columns are locked and hidden they are less likely to be edited, so I try to keep the stuff that is more likely to be accidentally broken as simple as possible.
Granted there isn't much complexity added when going from a SUM to a SUMIFS with only a single range/criteria set in the arguments. Its just the way I tend to work. Haha.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!