Forum Discussion
Date Lookup & Matching Formula Help
Hey experts,
Looking for some help with a date issue in my formulas. What we're looking to do is find all the dates in column M, then SUM the corresponding $ amounts in Column O.
So, if there were 5 bills due in March, it would find those 5 rows in column M, match them in Column O and sum it in another cell. This I have pretty much got working, however I have 2 issues:
1. All blank date cells are logged as a January date, so my January $$ amounts are very inaccurate (dates can't be put in ahead of time unfortunately so they need to be blank until confirmed)
2. My current formula works on Month only, so once the years overlap and we circle back to the same months the figures will get out of whack again, and due to the nature of building timelines we can't really separate it by year for this purpose.
The only 'year' formula I've been able to work out is this: =SUMIF(M5:M34,">=5/1/2023",O5:O34)
however, anything AFTER first of May calculates into this so it doesn't work
And my current best formula is this: =SUMPRODUCT((MONTH(M5:M34)=5)*(O5:O34))
However, as mentioned, once it circles back to the next May (or any month) the figures will start overlapping again.
Is anyone able to help with these? Appreciate any help, added column setup for reference.
2 Replies
- how about using SUMIFS like this:
=SUMIFS(O5:O34,M5:M34,">=5/1/2023",M5:M34,"<6/1/2023")
and you could make those dates 'dynamic' based on the location of the formula and what column or row or whatever it is in (i.e. what month it should be adding up)Hi mtarler
Looks like that worked, I did have to tweak it slightly but changing it to "<=31/5/2023" (I'm in Aus so the dates needed to be adjusted a touch) but this is adding stuff and isn't calculating blank cells. Thanks!
