SUMIF with multiple dates across columns without duplicating

Adam Kinney

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.

Mike Wilday

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?

Paul Newcome

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

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)

Adam Kinney

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

Mike Wilday

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?

Paul Newcome

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.

Adam Kinney

@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?

Mike Wilday

That's super peculiar. Try swapping the 1 and 0. Does that work?

Paul Newcome

I would try swapping the AND with an OR.

As for the duplicate dates... I'm not sure I follow.

Mike Wilday

@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.

Paul Newcome

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)

Mike Wilday

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)

Paul Newcome

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.