Formula to SUM 5 consecutive columns that match a criteria

fchri31

New Member
Joined
Sep 28, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
    excel help.PNG
    35.7 KB · Views: 23

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi & welcome to MrExcel.
How about
+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)))
 
Upvote 0
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
    excel help 2.PNG
    26.8 KB · Views: 9
  • excel formula working correctly.PNG
    excel formula working correctly.PNG
    3.1 KB · Views: 10
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top