SumIfs Using Date as a criteria.

[John Tron]

Hello, gg

I am trying to organize my sales data by sales category as a sum of all entries in that sales category if the date of the entry happened after the date of that sales category's last "Check-In Meeting", which I can adjust manually.

I know I am close, but am having trouble with the Date aspect. Please see the attached image. I used the first line to spell out my formula, its not getting the date part right, not sure how o fix it.

Any help would be greatly appreciated.


This is the formula I am using which does not work.

SUMIFS(I:I,E:E,E2,L:L,"<="&D2)


John

 

[Joe4]

There is nothing inherently wrong with the formula you have written, but then again you haven't told us exactly what the logic is supposed to be.
I think it would be better if you show us a small example, tell us what your formula is returning, and then tell us what it SHOULD return and why (explain the logic in plain English).

 

[John Tron]

Thank you so much for your reply, I didn't think anyone would respond.

You are correct, a smaller sized sample would be better (attached).

What I am trying to do it sum the dollars in column F of every Strategy, so to sum each strategy separately, only if that entry is entered after the date of that strategy's last "Checkdown Start Date" in Column H. So you see with the Strategy I have here, "Gulf Coast 12" I would like to sum all the Gulf Coast 12 lines (the F column), but only if their column H occurred before the column D.

Once a "Checkdown Start Date" occurs, al info that was entered before that new "Start Sate" becomes irrelevant to track the need for another potential Checkdown Start Date in the future.

Essentially The "Checkdown Start Date" marks the last time the data was reset in management's eyes, and the summing process for that Strategy starts over at 0.

There are 19 strategies and hundreds of lines, with a new line entered each day for each strategy with that days date registered in column D. I am hoping just to have 19 lines in column K which report back the running sum for each strategy since the last Checkdown.

My current formula seems to work, in a way, but it doesn't register the dates, it just sums all of that strategy, regardless of the dates in relation to column D and H.

Thank you so much for your help.

 

[Joe4]

Your simplified data is a bit confusing, as you removed columns, so the formula you posted no longer coincides with your data (I just wanted you to remove rows, not column, though show the column which you reference in your formula but is hidden in your first image).

And I don't know that date logic doesn't follows (or I am not understanding what you are saying).

Can you walk through the three lines in your example, and explain between lines 2, 7, and 12, which lines are to be included and which lines are not.
Because as far as I can see, H2, H7, and H12 ALL come after D2.

Is each line being compared to the value in cell D2, or the value in column D of each particular row?
Even if that is the case, all the values in D2, D7, and D12 are less to their counterparts in H2, H7, and H12.

So either way, it appears that the logic is faulty.

 

[John Tron]

Sorry about that, I see how it screwed things up when i cut the columns to simplify.

Here is my next stab at simplification:

I have changed the strategies so that there are only 3 (Strategies "1", "2" and "3"). Each with their own Checkdown Start Date. Dec 22th 2022 for Strategy 1, Jan 4 2023 for Strategy 2, and Jan 2nd 2023 for Strategy 3.

Each strategy makes the same each day, for simplicities sake, 100k/day for Strategy 1, 200k/day for Strategy 2 and 300k/day for Strategy 3.

For the sample "COB Current Day"s I am using the first 5 days in 2023. Each Strategy reports once and only once/day.

The idea is that I only want to add up the strategy earnings for days after the that specific strategy's Strategy Checkdown day, in column "L".

This would mean Strategy 1 would get credit for all 5 days of Sales, as its "L" column Checkdown Start Date took place before any of the 5 days of the "COB_CurrectDay" (Dec 22th happened before all the first 5 days of Jan23 which make up the sample COB Dates Data in "D"): Strategy 1 should record all 5 days of 100k/day earnings, 5*100k so 500k.

Strategy 2's Checkdown Start Date "L" column is Jan 4, so it would only sum COB days after Jan 4, meaning only Jan 5th in this sample data, the last day: Strategy 2 should record 1 day of earning 200k/day, 1*200k, so 200k.

Strategy 3's Checkdown Start Date "L" column is Jan 2 so it would only sum COB days after Jan 2, meaning just Jan 3rd, 4th and 5th in this sample data, but not the days before it in column "L": Strategy 3 should record 3 days of earning 300k/day, 3*300k, so 900k.

I will manually change the dates in L as Checkdown Start Dates come up every few months. I am hoping the formula can automatically adjust to compare the L dates and the D Dates and see if that day's entry should be part of that Strategy's summed total or not.

Thank you so much for your help.

 

[Joe4]

OK, so you have your dates backwards (column L is the criteria column, not column D).
And if you want "greater than" only, you need to remove the "=" from your equation.

So for your last posted example, but this in cell P2 and copy down to P4:

=SUMIFS(F:F,E:E,O2,D:D,">" & L2)