Formula to SUM 5 consecutive columns that match a criteria

[fchri31]

Hi everyone,

This is my first time using this forum so apologies if this question has been asked before.

I am trying to create a formula that adds current (within 1 year) accounts and another formula that calculates long term (more than 1 year) accounts.

The current accounts are always the 5 consecutive columns from the current year and current quarter. For example, we are in year 2020, Q2. So the current accounts will equal, 2020 Q2 (Column M), 2020 Q3 (Column N), 2020 Q4 (Column O), 2021 Q1 (Column P), 2021 Q2 (Column Q). Next quarter, for Q3, it will be 2020 Q3 (Column N), 2020 Q4 (Column O), 2021 Q1 (Column P), 2021 Q2 (Column Q), 2021 Q3 (Column R)

The long term accounts will always equal the 6th column from the current year and quarter, so in 2020 Q2 case, from 2021 Q3 and onwards.

This formula needs to roll forward, so if I were to update the values in cell C2 and C3, columns H and I should populate for that Quarter and Year's current and long term accounts amounts

There may be an easier way of doing this regarding the format of the excel, so any help would be greatly appreciated. Thank you all for your time.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2			2020
3			2
4
5												2020	2020	2020	2020	2021	2021	2021	2021	2022	2022	2022	2022	2023	2023	2023	2023
6												Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
7								20	45			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
8
Main

Cell Formulas
Range	Formula
H7	H7	=SUM(FILTER(J7:AA7,($J$5:$AA$5&$J$6:$AA$6>=$C$2&"Q"&$C$3)*($J$5:$AA$5&$J$6:$AA$6<=$C$2+1&"Q"&$C$3)))
I7	I7	=SUM(FILTER(L7:AA7,($L$5:$AA$5&$L$6:$AA$6>$C$2+1&"Q"&$C$3)*($L$5:$AA$5&$L$6:$AA$6<=$C$2+2&"Q"&$C$3+1)))

 

[fchri31]

Hi Fluff,

Thank you so much for your reply, it ended up working. I just needed to change every AA to AM and it is working like a charm!!

With that being said, the only issue that I am having is on row 15 for the long term column (column I). It appears that all of column "I" has a green triangle in the top left which are all working properly and correctly besides the selected cell with $175,000 (this is incorrect). As you can see this cell has no green triangle and the total is also incorrect. I have dragged the formula down as well as copied and pasted from a correct formula to this cell and still I am having issues.

I have attached a screenshot of the table showing the formula of cell I15 as well as the green triangles I am referring too. I have also attached the formula that is working correctly for I16.

Thank you again for all your help, you are a lifesaver.

 

[Fluff]

If you hover the mouse over the green triangle what does it say?
Also is the value of 175,000 too great or too small?

 

[fchri31]

It says "The formula in this cell refers to a range that has additional numbers adjacent to it." This is just because there is data from irrelevant older years in columns J-K. Once I delete these two columns the green triangle disappears for all the cells. In I15, the 175,000 remains, and is incorrect.

175,000 is too small. 175,000 exists repeatedly in column T, X, AB, AF. In AJ, the amount is 675,000, so cell I15 should equal 1,375,000. At first I thought it was because this row contains the same value multiple times, but a similar instance (with same values) occurs for another account and it is still correct.

Thanks again for your continued help.

 

[Fluff]

It says "The formula in this cell refers to a range that has additional numbers adjacent to it."

Ok that's fine, it's just a general warning & can be ignored.

175,000 is too small.

Should long term be reading all columns from the start point onwards, or just 5 columns as per col H?

 

[fchri31]

Long term should just be reading from the first column after the current columns and onwards to column AM. For example, in 2020 Q2, the current accounts will equal 2020 Q2 (Column M), 2020 Q3 (Column N), 2020 Q4 (Column O), 2021 Q1 (Column P), 2021 Q2 (Column Q). The long term accounts should equal 2021 Q3 (Column R) to 2026 Q4 which is Column AM.

It seems to be that the formula is not adding all of the columns that it should be, and is only taking the first number that appears. Just playing around with the reference in C2 and C3, if I make it 2023 and 4, it shows 175,000 still, but when I change it to 2023 and 4, it shows 175,000 but should be (175,000 + 675,000). If I change it to 2024 and 4, it shows 675,000, which is correct.

What's strange is that the Long term accounts work perfectly fine for other accounts, its just this cell (I15) that is having difficulty.

 

[fchri31]

Just playing around with the reference in C2 and C3, if I make it 2023 and 4, it shows 175,000 still, but when I change it to 2023 and 4, it shows 175,000 but should be (175,000 + 675,000). If I change it to 2024 and 4, it shows 675,000, which is correct.

This should be "Just playing around with the reference in C2 and C3, when I change it to 2023 and 4, it shows 175,000 but should be (175,000 + 675,000). If I change it to 2024 and 4, it shows 675,000, which is correct. "

Apologies.

 

[Fluff]

In that case use

=SUM(FILTER(J7:AM7,$J$5:$AM$5&$J$6:$AM$6>$C$2+1&"Q"&$C$3))

What's strange is that the Long term accounts work perfectly fine for other accounts

No it's not, it was summing 5 columns only.

 

[fchri31]

In that case use

=SUM(FILTER(J7:AM7,$J$5:$AM$5&$J$6:$AM$6>$C$2+1&"Q"&$C$3))

No it's not, it was summing 5 columns only.

Wow! Awesome, now it is working. Thank you so much for all your help. You are a legend!