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

Mike Meyer

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!

Paul Newcome

Are you able to provide a screenshot for reference?

Mike Meyer

Paul Newcome

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}?

Mike Meyer

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!

Paul Newcome

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.

Mike Meyer

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. 👍

crachon

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?

Paul Newcome

@crachon

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.

crachon

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

Paul Newcome

@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