Sum formula using another cell to input a change in the formula

[lonmfrech]

We keep track of the tons that we haul in our trucks. Each month since 1997 I have entered the tons we hauled each month. The data is categorized by the year and then the months for that year.

Each month of the current year, I want to be able to compare the amount of tons brought in from the beginning of the year to the current month of the current year to the past years from the beginning of the year to the current month for that past year.

In this example, I want the sum in the “Tons to Month” row for the 2021 column to Sum Jan through Jun, and I want the column for 2022 to add Jan through Jun of 2022.

Currently, I just make a Sum formula in “Tons to Month” and add up the tons from Jan to Jun for each year.

Instead, what I want to do is have a formula in “Tons to Month” that would point to a cell where I enter “Jun” (this happens to be the current month that I am looking for the data for) and the formula returns the Sum of Jan through Jun. This way, I only enter “Jun” in one cell, and all the “tons to Month” get updated in each years’ column.

Year	2021	2022
Tons to month	16,376	16,299
Jan	2,572	2,635
Feb	2,352	2,327
Mar	2,876	2,767
Apr	2,877	2,638
May	2,638	2,813
Jun	3,062	3,117
Jul	3,121	102
Aug	2,925	0
Sep	2,747	0
Oct	2,720	0
Nov	2,784	0
Dec	2,608	0

 

[Ezguy4u]

lonm, I usually get directions wrong. But we want to get the ball rollin. Now I came up with a solution that I thought should work. So below is the output. If you have some other idea for output, let us know. So the sum formula only starts when a number is entered into the new month. Let the decision begin.

Book1
	A	B	C	D	E
1	Year		2021	2022
2	Tons to month		16376	16299
3	Jan	18948	2572	2635	18934
4	Feb	21300	2352	2327	21261
5	Mar	24176	2876	2767	24028
6	Apr	27053	2877	2638	26666
7	May	29691	2638	2813	29479
8	Jun	32753	3062	3117	32596
9	Jul	35874	3121	2500	35096
10	Aug		2925	200
11	Sep		2747	0
12	Oct		2720	0
13	Nov		2784	0
14	Dec		2608	0
Data

Cell Formulas
Range	Formula
B3:B14	B3	=IF(D4>0,SUM($C$2:C3),"")
E3:E14	E3	=IF(D4>0,SUM($D$2:D3),"")

 

[lonmfrech]

Hi Ezguy4u,
If I understand your suggestion, this is essentially what I am doing, which is changing the Tons To Month formulas each month.
I would need to change your formula
=IF(D4>0,SUM($C$2:C3),"") from D4 to D5 each month in each of the “Tons to Month” column as the new data comes in to get the new sum for the Tons to Month.
(I should have included rows and columns in my post)
Using your spreadsheet example for cell identification, the way I’m currently doing it, I would change the Sum formula in each of the Tons to Month. C2, For May 2021, would be =Sum(C3:C7),
then for June 2021 it would be =Sum(C3:C8).

In your example for the same month to compare of May then June, the formula would change from
=IF(D7>0,SUM($C$2:C3),"") for tons through May to
=IF(D8>0,SUM($C$2:C3),"") for tons through June.

Maybe a better way of asking the question is:

Book1.xlsx
	A	B	C	D	E	F
1	Current formuals for:
2	Tons to Month Formulas in Column B and C to compare 21 to 22 Jan through April
3
4	Year	21	22
5	tons to month	10,676	10,368
6	Jan	2,572	2,635
7	Feb	2,352	2,327
8	Mar	2,876	2,767
9	Apr	2,877	2,638
10	May	2,638	2,813
11	Jun	3,062	3,117
12	Jul	3,121	-
13	Aug	2,925	-
14	Sep	2,747	-
15	Oct	2,720	-
16	Nov	2,784	-
17	Dec	2,608	-
18
19	Looking for a formula that will allow me to populate a cell with the month that I want to compare the Tons to Month to
20	May	Example: Popluate this cell(A20) with "May", and the formula in B23 and C23 changes to Sum the values in B24:B28, because B28 is the row that May is in.
21
22	Year	21	22
23	tons to month	13,315	13,182	Note: B28 in the formula for Tons to Month would be based on the value "May" placed in cell B20
24	Jan	2,572	2,635
25	Feb	2,352	2,327
26	Mar	2,876	2,767
27	Apr	2,877	2,638
28	May	2,638	2,813
29	Jun	3,062	3,117
30	Jul	3,121	-
31	Aug	2,925	-
32	Sep	2,747	-
33	Oct	2,720	-
34	Nov	2,784	-
35	Dec	2,608	-
Sheet1

Cell Formulas
Range	Formula
B5:C5	B5	=SUM(B6:B9)
B23:C23	B23	=SUM(B24:B28)

 

[Ezguy4u]

lonm How about this one? Now note the Data Validation in A20. This just makes sure you have the correct month spelling. Anyway let us know if you have any problems or questions.

22-07-18.xlsx
	A	B	C
20	May
21
22	Year	21	22
23	tons to month	13314.8474	13181.7204
24	Jan	2572.1095	2635.42
25	Feb	2352.001	2327.417
26	Mar	2875.6065	2767.4649
27	Apr	2876.682	2638.071
28	May	2638.4484	2813.3475
29	Jun	3061.6413	3117.31
30	Jul	3121.4789	0
31	Aug	2924.749	0
32	Sep	2746.7116	0
33	Oct	2719.528	0
34	Nov	2784.1482	0
35	Dec	2607.508	0
Data 2

Cell Formulas
Range	Formula
B23	B23	=SUM($B$24:INDEX($B$24:$B$35,MATCH($A$20,$A$24:$A$35,0)))
C23	C23	=SUM($C$24:INDEX($C$24:$C$35,MATCH($A$20,$A$24:$A$35,0)))

Cells with Data Validation
Cell	Allow	Criteria
A20	List	=$A$24:$A$35

 

[lonmfrech]

That's it!! Thanks for your quick response

 

[Ezguy4u]

Glad I could help. If you have any other problems or questions, let us know.