SUMIF with multiple dates across columns without duplicating

Options

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

Answers

  • Adam Kinney
    Adam Kinney ✭✭✭✭
    Options

    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

  • Adam Kinney
    Adam Kinney ✭✭✭✭
    edited 11/17/20
    Options

    @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
    Mike Wilday ✭✭✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I would try swapping the AND with an OR.


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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!