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

✭✭✭✭
edited 06/02/23

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))

• ✭✭✭✭✭✭

Are you able to provide a screenshot for reference?

• ✭✭✭✭
• ✭✭✭✭✭✭

I'm not sure I follow what you are trying to accomplish. Are you trying to get it to compare where system date is equal to Today {H} regardless of what is in Today {H}?

• ✭✭✭✭

Hey Paul, thank you for the help! Yes, I'm simply trying to sum an invoice column where a date in 1 date column matches a date in another date column. I have other sheets where the second column won't be a today column, where the date will be the same throughout the column.

Sum invoice amounts if date in Column A = date in Column B.

Thank you!

• ✭✭✭✭✭✭

If the date will be the same in the helper column (even if it isn't today's date) you can still use the same logic.

If the date in the second column will not be the same date down the entire column then you would need to create a helper column (can be hidden after setting up) to check a box when the dates match and then include this as a range/criteria set in your SUMIFS.

• ✭✭✭✭

Thank you, Paul. I was hoping there was a way to accomplish this through a formula alone, without helper columns (sheet data limits), but I'll find a work around for that too. 👍

• ✭✭

I don't think this is the same question but is there not a way to just = to another cell date? All I want to do is literally equal one start date to another. Is there no way to do this? Does this have to be a dependency?

• ✭✭✭✭✭✭

If you are using dependencies and the column you want this in is being referenced by the dependencies, then you will have to create the dependency using predecessors as columns being used by the dependencies feature cannot house formulas.

If you are not using the built in dependencies feature, then yes. You can do a straight cell reference.

• ✭✭

there are no dependencies. is it not just = cell? i think i am having a moment here

• ✭✭✭✭✭✭

@crachon Screenshots would help immensely, but in general you can use

=[Column Name]@row

or if the cell is on a different row you would replace "@row" with the appropriate row number.

=Column Name]5

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!