Sumif Match within a Date Range Help

Jonna Critchley

Hi There,

I'm working with this formula:

=SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, ="Initiative Name", {Weekly Sheet - Company Name Range 3}, >=DATE(2023, 10, 1), {Weekly Sheet - Company Name Range 3}, <=DATE(2023, 10, 31))

This formula pulls from a another sheet and says if it's this initiative name and between these dates sum the time column.

I'm trying to figure out how to enhance this formula to instead of naming the initiative in the formula, instead saying something like if the initiative name in my current sheet @ this row matches the initiative name from the other sheet, and is between these dates, it will sum the time column.

I'd also like to enhance this formula to point to a cell that I can change the date range on that would then change it where ever referenced, instead of having to change the date range each fiscal on every single formula - if that make sense.

Appreciate any assistance in advance!

Jonna Critchley

Figured this out! Please disregard :)

JamesB

@Jonna Critchley

You can simplify your formula to use dates in your sheet and use the AND function. When I am creating a metric sheet, instead of creating helper columns all the time, I have started to use the field in my sheet summary area for 1:1 references when I want to create column formulas referencing the same data.

=SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, [Initiative Name]@row, {Weekly Sheet - Company Name Range 3}, AND(@cell >= [Start Date]#, @cell <= [End Date]#))

The [Field Name]# refers to the name of the field in the sheet summary area.

For your initiative name, you can reference the @row for the column that has the initiative name.

Hope this helps.