Return Latest Month Value

[kumara_faith]

Hi,

I have the following tables;

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Table 1														Table 2	Tuesday, 31 January, 2023
3	Branch	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Branch	Sales
4	Texas	300													Texas	100
5	New York	300													New York	100
6	Chicago	300													Chicago	100
7	Total	900													Texas	100
8															New York	100
9															Chicago	100
10															Texas	100
11	Table 3														New York	100
12	Total	900													Chicago	100
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=SUMIF($Q4:$Q12,C4,R4:R12)
D7	D7	=SUM(D4:D6)
D12	D12	=LOOKUP(2,1/(D7:O7>0),D7:O7)

Table 2 has the raw data. This data is updated daily.
Table 1 is the summary from Table 2

In Table 3, I am trying to take the total for the latest date. Example if today is Jan 31, then the total will show 900. However, when the date changes to Feb 1 2023 in cell R2,, the total in Table 1 which is based from Table 2 will change as well. Assuming today is Feb 1 2023, an example of the new tables will look as below:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Table 1														Table 2	Tuesday, 31 January, 2023
3	Branch	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Branch	Sales
4	Texas	300	700												Texas	100
5	New York	300	300												New York	100
6	Chicago	300	300												Chicago	100
7	Total	900	1300												Texas	500
8															New York	100
9															Chicago	100
10															Texas	100
11	Table 3														New York	100
12	Total	1300													Chicago	100
Sheet1

Cell Formulas
Range	Formula
D7:E7	D7	=SUM(D4:D6)
D12	D12	=LOOKUP(2,1/(D7:O7>0),D7:O7)

Is there a way to build a formula in Table 1 that takes the sum from Table 2 only for the respective months based on the dates in cell R2 and the figures in Table 2?

 

[Sam_D_Ben]

Yes possible by matching "Months"

 

[kumara_faith]

Hi,

How do I build a formula to accomplish this ? Appreciate all the help.

 

[Sufiyan97]

Not getting your requirement, can you please explain further, you are getting data for Feb in your example, now what do you want?

 

[excel_learnerz]

Yes, you can use the SUMIFS function to sum the values in Table 2 based on the respective months in the dates column (assuming the dates are in column Q). Here's an example formula you can use in Table 1:

=SUMIFS(Table2[Value],MONTH(Table2[Date]),MONTH(R2),YEAR(Table2[Date]),YEAR(R2))

In this formula, "Table2[Value]" refers to the column in Table 2 containing the values you want to sum, and "Table2[Date]" refers to the column containing the dates.

The MONTH function extracts the month from the dates in the Table 2 date column, and the YEAR function extracts the year. These values are then compared to the month and year in cell R2 using the SUMIFS function, which only adds up the values in Table 2 that meet both criteria.

Note that you'll need to adjust the ranges and column names in this formula to match your specific table layout.

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2			Table 1														Table 2	2/28/2023
3			Branch	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Branch	Sales
4			Texas	0	700	0	0	0	0	0	0	0	0	0	0		Texas	100
5			New York	0	300	0	0	0	0	0	0	0	0	0	0		New York	100
6			Chicago	0	300	0	0	0	0	0	0	0	0	0	0		Chicago	100
7			Total	0	1300	0	0	0	0	0	0	0	0	0	0		Texas	500
8																	New York	100
9																	Chicago	100
10																	Texas	100
11			Table 3														New York	100
12			Total	1300													Chicago	100
13
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
D4:O6	D4	=SUMPRODUCT($R$4:$R$12*(MONTH($R$2)=MONTH(D$3&1)*($Q$4:$Q$12=$C4)))
D7:O7	D7	=SUM(D4:D6)
D12	D12	=LOOKUP(2,1/(MONTH($R$2)=MONTH(D3:O3&1)),D7:O7)

 

[kumara_faith]

Hi Sufian and excel_learnerz

Thank you and appreciate it.

The data is updated daily from 1 Jan to 31 Dec. Is there a way the formula could retain the previous month data when the dates cross over to the next month?

 

[Sufiyan97]

I only know one way to do it to keep monthly columns in table 2.
I don't have any other idea to do it with formula.
Hope someone else can help you in this matter.
Good luck.

 

[excel_learnerz]

you can modify the formula to retain the previous month's data even when the dates cross over to the next month. To do this, you can use an IF statement to check whether the date in cell R2 is the first day of a month. If it is, the formula will sum the data for the previous month. If it's not, the formula will sum the data for the current month.

Here's the modified formula:

=IF(DAY(R2)=1, SUMIFS(Table2[Value], MONTH(Table2[Date]), MONTH(R2)-1, YEAR(Table2[Date]), IF(MONTH(R2)=1, YEAR(R2)-1, YEAR(R2))), SUMIFS(Table2[Value], MONTH(Table2[Date]), MONTH(R2), YEAR(Table2[Date]), YEAR(R2)) )

This formula checks if the day of the date in cell R2 is the first day of the month using the DAY function. If it is, the formula calculates the sum for the previous month using SUMIFS. If the month is January (MONTH(R2)=1), it also adjusts the year to the previous year. If the day is not the first day of the month, the formula calculates the sum for the current month as before.

Remember to adjust the ranges and column names in this formula to match your specific table layout.

 

[RoryA]

=IF(DAY(R2)=1, SUMIFS(Table2[Value], MONTH(Table2[Date]), MONTH(R2)-1, YEAR(Table2[Date]), IF(MONTH(R2)=1, YEAR(R2)-1, YEAR(R2))), SUMIFS(Table2[Value], MONTH(Table2[Date]), MONTH(R2), YEAR(Table2[Date]), YEAR(R2)) )

Did you test that at all? It will not work since SUMIFS requires actual ranges for its sum and criteria ranges, not arrays. The use of MONTH and YEAR there will create arrays.