# Formula to SUM 5 consecutive columns that match a criteria

#### fchri31

##### New Member
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.

#### Attachments

• excel help.PNG
35.7 KB · Views: 18

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff New.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
22020
32
4
52020202020202020202120212021202120222022202220222023202320232023
6Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4
7204512345678910111213141516
8
Main
Cell Formulas
RangeFormula
H7H7=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)))
I7I7=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

##### New Member
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.

#### Attachments

• excel help 2.PNG
26.8 KB · Views: 4
• excel formula working correctly.PNG
3.1 KB · Views: 4

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### New Member
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

##### MrExcel MVP, Moderator
In that case use
Excel Formula:
``=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

##### New Member
In that case use
Excel Formula:
``=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!

Replies
11
Views
143
Replies
4
Views
143
Replies
16
Views
363
Replies
2
Views
133
Replies
0
Views
97

1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back