SUMIFS question

Travis Myers

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.

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

Travis Myers

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.