SUMIFS and CRITERIA where @cell date = date in associated row in another column

Mike Meyer
Mike Meyer ✭✭✭✭
edited 06/02/23 in Formulas and Functions

Hello friends,

Basic need description: SUM the invoice amounts in Column A IF date in Column B = date in Column C

Using SUMIFS function: I am trying to SUM $'s in Column: "πŸ’² Total Rental $ Amount {H}" β†’ if @cell date in Column: "πŸ“… System Date" = date in Column: "πŸ“… Today {H} πŸ…°"

Column: "πŸ“… Today {H} πŸ…°" is populated every morning with a simple automation (Run Everyday, If the date in this column is in the past, then Record a Date in this column)

Column: "πŸ“… System Date" is populated every morning by a Data Shuttle workflow.

Current, below, formula works, but I don't want the criteria to just reference [πŸ“… Today {H} πŸ…°]1 (cell 1 in that column). I'd like for the criteria to reference the cell in the same row as the cell in the criteria range. (I'd like to be able to use this formula in other sheets where the two date columns will not be a today reference)

=SUMIFS([πŸ’² Total Rental $ Amount {H}]:[πŸ’² Total Rental $ Amount {H}], [πŸ“… System Date]:[πŸ“… System Date], @cell = ([πŸ“… Today {H} πŸ…°]1 - 1))

Thank you for your help!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!