SUMIFS question

On one of my sheets, I am summing a range based off of another columns date range and another columns value (true/false).

Currently, the formula I am using is:

=SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1)


However, there are times when the data I need is another another column other than {JOBS AWARDED Lighting}. So for a given row, we might have a value in the column {JOBS AWARDED Lighting}, but IF a value exist in another column, I need it to use that value instead of the one in {JOBS AWARDED Lighting} for that row.


How can I do that? I am unable to wrap my head around it with the syntax Smartsheet's uses.

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Travis Myers

    If I understand, you need to check a cross sheet reference to see if a cell has a value. If it does, Sum using that, if it doesn't, use the sumifs you have written.

    One solution is using the COUNTIFS to see if the other cell is blank (or, as I have written, not blank)

    Not knowing if the criteria were the same, I only illustrated the strategy.

    IF(COUNTIFS({Another column},@cell<>"")>1, SUMIFS(for another column + criteria), =SUMIFS({JOBS AWARDED Lighting}, {JOBS AWARDED Bid Date}, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31)), {JOBS AWARDED Range 4}, @cell = 1))

    Kelly

  • I believe you are correct.

    To try and explain it a bit further, they are bid/buy columns. Sometimes the buy amount is the exact same as the bid, so the buy is left blank. In that instance I would sum using the bid value. But if the buy column has a value, I would need the value in bid ignored.


    I will try your formula and see if it does what I am after.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!