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!!

 

[6StringJazzer]

Use a Table for your data, then the formula can use structured references that do not have to explicitly specify the number of rows.

What cell contains this formula, and what does your data look like?

 

[nniedzielski]

The formulas are in Cells U4 through U15, one for each month. Data is just rows of projects with implementation dates and cost savings. so like:

Status	Imp Date	Annual Savings
Implemented	1/31/2024	$20
In-Progress	5/26/2024	$900

 

[StevenE06]

Column P is the Imp Date
Column Q is the Annual Savings
Is $T4 a single cell that shows current date or is column T full of dates as well?

Does the Status column of Implemented and In-Progress change if you calculate the savings or not?

EDIT: NVM I'm assuming T4 to T15 are the Months and U4 to U15 is calculating monthly savings

 

[6StringJazzer]

What's in column T? Where is this formula?

This gives you a rough idea of what I'm talking about based on some guessing about what your sheet looks like. The first block of data is the structured table. Because it's structured, we use the column headers to identify data so it doesn't matter what columns the data is in. The second block is a list of month numbers and the corresponding formula based on your first post.

$scratch.xlsm
	A	B	C
1	Status	Imp Date	Annual Savings
2	Implemented	1/31/2024	20
3	In-Progress	5/26/2024	900
4	In-Progress	1/15/2024	658
5	In-Progress	2/15/2024	6
6	In-Progress	3/15/2024	533
7	In-Progress	1/15/2024	865
Sheet16

$scratch.xlsm
	T	U
1	Month	Some Formula
2	1	1543
Sheet16

Cell Formulas
Range	Formula
U2	U2	=SUM(IF(MONTH(Table1[Imp Date])=MONTH(T2),Table1[Annual Savings],0))

 

[nniedzielski]

any way to do this with a formula and avoid a table?

 

[StevenE06]

Try this

=SUMIF($P:$P, $T4,  $Q:$Q)

This will sum everything in column Q if the month listed in T4 matches the month entered in Column P

 

[StevenE06]

If Implemented/In-Progress matter, use

=SUMIFS($Q:$Q, $P:$P, $T4, $O:$O, "Implemented")

 

[nniedzielski]

Try this

=SUMIF($P:$P, $T4,  $Q:$Q)

This will sum everything in column Q if the month listed in T4 matches the month entered in Column P

View attachment 110210

I tried this and it makes sense, but i am getting: should be getting $1,800,000

 

[StevenE06]

I tried this and it makes sense, but i am getting: should be getting $1,800,000

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

EDIT:
Also is your month column Jan-24 type in and formatted as General or is it 1/1/2024 formatted as Date to display MMM-YY