SUMIF If a Specific Month is Within Start and End Date Range

[splendidus]

Hey, guys!

I'm trying to construct a relatively simple (or so I thought) formula that would evaluate each line and SUMIF for each calendar month based on category (that's easy) and if a given month is within a Start and End date for each line.

The formula works, BUT only if each line item's Start date is 1st of month. But I'd like non-first lines to be SUMIF'd as well. Please have a look at the attached example. In Row 189, the Start date is 12 March, and the formula does NOT include this row in SUMIF, but I need to include such items as well. If I change 12 March to 1 March - it works fine.

Any ideas on how to finish that (darn) formula?
Thanks so much in advance!

[Melvosh]

Right now, your formula checks to see if the dates in column B are before or on the first day of a given month this year, and if the dates in column C are on or after the last day of a given month this year. The formula is doing what it is supposed to. Changing the date to the first of the month in that row makes it meet the specified criteria.

Can you give an explanation of what exactly you want to SUM?

[Sam Capricci]

I used this formula in I7 dragged down and right... =SUMPRODUCT($D$3:$D$214,($A$3:$A$214=$H7)*($B$3:$B$214<=I$5)*($C$3:$C$214>=I$5))
BUT, it only works if you change Row 5 from "Jan" and "Feb" to 1/1/2019 and 2/1/2019 (respectively) and drag that across the row and you can format as "ddd" in custom.

EDIT, if you change the dates in row 5 to the end of those months (1/31/2019 and 2/28/2019) then drag that across you will catch those that you have noted in row 189 as being missed.

[splendidus]

Right now, your formula checks to see if the dates in column B are before or on the first day of a given month this year, and if the dates in column C are on or after the last day of a given month this year. The formula is doing what it is supposed to. Changing the date to the first of the month in that row makes it meet the specified criteria.

Can you give an explanation of what exactly you want to SUM?

I need to adjust the formula (e.g. in I7) to work on cells that are not 1st of the month. For instance in Row 189, the Start date is 12 March - currently the formula does not include it, but I need it to. So my question is: how do I adjust I7 formula to include rows such as 189.

[splendidus]

Guys, any ideas on how to approach this? I can't believe nobody can do this here.

Again, just to simplify the question: how do I adjust the formula in I7 so it included rows where Start date isn't first of month (e.g. Row 189)?

I'd appreciate literally any pointers here! Thanks so much in advance!! :D

[AliGW]

Administrative Note:

We don't expect you to bump threads more than once in 24 hours - it has been only ajust over 12 hours since you last posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[GeoffW283]

@splendidus - I don't see any response to post #3 above. Can you explain what, if anything, is wrong with Sam's proposed solution (other than I think he meant "mmm" for the custom format rather than "ddd")?

[Sam Capricci]

other than I think he meant "mmm" for the custom format rather than "ddd"

Yes, that is what I meant, how did you read my mind?

[GeoffW283]

Thanks for the rep for my mind reading skills

[splendidus]

@splendidus - I don't see any response to post #3 above. Can you explain what, if anything, is wrong with Sam's proposed solution (other than I think he meant "mmm" for the custom format rather than "ddd")?

My apologies, for some reason I thought I replied to that, but I did not. Here's why it doesn't quite work for me:

BUT, it only works if you change Row 5 from "Jan" and "Feb" to 1/1/2019 and 2/1/2019 (respectively) and drag that across the row and you can format as "ddd" in custom.

The table I'm working with is for work, and due to reporting reasons the format's fixed, meaning I can't really change it much so I have to work with what I have :/ Unless I'm missing something and I don't have to change the dates format.

[GeoffW283]

due to reporting reasons the format's fixed

With the suggested changes to row-5, visually there would be no change. Row-5 would still look like Jan, Feb, Mar, Apr etc, even though the underlying date values were different. So if "visually identical" is sufficient for your boss then Sam's solution should work for you (with "ddd" changed to "mmm" of course).

[splendidus]

With the suggested changes to row-5, visually there would be no change. Row-5 would still look like Jan, Feb, Mar, Apr etc, even though the underlying date values were different. So if "visually identical" is sufficient for your boss then Sam's solution should work for you (with "ddd" changed to "mmm" of course).

That's the thing, visually identical won't cut it in my case. Basically, I get a file off a reporting cube in the system that outputs in a very specific, pre-defined format - which I'm not allowed to touch due to variety of internal (=company) reasons, un-VERY-fortunately. So I'm forced to find solutions that would work WITHOUT changing the underlying data structure in any way. Sucks to be me, I know, but that's how corporate life is some(all the?)times...

Overall, I feel that I'm pretty close to a solution now: the formula in I7 works for everything, other than Start dates different than 1st of the month - that's what I can't crack.

[GeoffW283]

First I just want to check that I am counting correctly. In the following diagram the grey bars are the start and end dates and col-A is my assumption for which need to be counted for the month of February.

counting.png

If so then I think the following slight tweak to your formula works (changes highlighted in red). No other changes to the worksheet are needed. In I7 copied down and across:

Please Login or Register  to view this content.

I've tested as best I can but please verify carefully!