Monthly Goal Formula

[nniedzielski]

I have this formula

=SUM(IF(MONTH($P$4:$P$40)=MONTH($T4), $Q$4:$Q$40, 0))

It is looking at an implementation date in column P, and if it matches the Month in Column T, then it calculates the monthly savings in column Q. The issue is, I keep having to change the Range as I add rows monthly, right now it goes down to P40. If i set it to like P50 to give myself ten rows of play, the formula goes #N/A. How can i adjust this formula to where i dont have to keep tinkering with it?

As always, thank you so much!!

 

[StevenE06]

Also: Is your Jan-24 column entered as "Jan-24" or is it 1/1/2024 formatted as Jan-24?

 

[nniedzielski]

Can you show the formula entered and the sheet view like I did?

yes sir, here you go

 

[nniedzielski]

Also: Is your Jan-24 column entered as "Jan-24" or is it 1/1/2024 formatted as Jan-24?

i just noticed its 1/1/2024, think that could be a the problem?

 

[StevenE06]

The problem is, is that it is comparing 1/31/2024 to 1/1/2024.

Try this

=SUMIFS($Q:$Q, $P:$P, ">=" & $T4, $P:$P, "<=" & $T4 + DAY(EOMONTH($T4,0) -1))

 

[nniedzielski]

ok, that works for January, but when i copied down to february, that formula ignored a 2/5 savings. not sure why

The problem is, is that it is comparing 1/31/2024 to 1/1/2024.

Try this

=SUMIFS($Q:$Q, $P:$P, ">=" & $T4, $P:$P, "<=" & $T4 + DAY(EOMONTH($T4,0) -1))

 

[StevenE06]

ok, that works for January, but when i copied down to february, that formula ignored a 2/5 savings. not sure why

Hmm thats odd

I just updated mine and drug the formulas down and they worked fine

The formulas should be
Jan -

=SUMIFS($Q:$Q, $P:$P, ">="&$T4, $P:$P, "<="& $T4 + DAY(EOMONTH($T4,0)-1))

Feb -

=SUMIFS($Q:$Q, $P:$P, ">="&$T5, $P:$P, "<="& $T5 + DAY(EOMONTH($T5,0)-1))

Mar -

=SUMIFS($Q:$Q, $P:$P, ">="&$T6, $P:$P, "<="& $T6 + DAY(EOMONTH($T6,0)-1))

The $T# should go from 4 to 5 to 6 etc as it drags down.

 

[nniedzielski]

Hmm thats odd

I just updated mine and drug the formulas down and they worked fine
View attachment 110216
The formulas should be
Jan -

=SUMIFS($Q:$Q, $P:$P, ">="&$T4, $P:$P, "<="& $T4 + DAY(EOMONTH($T4,0)-1))

Feb -

=SUMIFS($Q:$Q, $P:$P, ">="&$T5, $P:$P, "<="& $T5 + DAY(EOMONTH($T5,0)-1))

Mar -

=SUMIFS($Q:$Q, $P:$P, ">="&$T6, $P:$P, "<="& $T6 + DAY(EOMONTH($T6,0)-1))

The $T# should go from 4 to 5 to 6 etc as it drags down.

I just retried the January and this time it grabbed a 2/5 date and added it on.

 

[StevenE06]

I just retried the January and this time it grabbed a 2/5 date and added it on.

So is it working correctly now?

You want the Jan-24, Feb-24, Mar-24 etc to all be 1/1/24, 2/1/24, 3/1/24 etc