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]

Glad to help & thanks for the feedback.

 

[fchri31]

Hi again,

Thank you for your help above, but I was wondering you could assist again with something similar. So we are still using the original formula you provided us as a check, but we wanted to recreate the format of the file. The format now looks like the attached picture, in which Column I contains the pertinent amount that we want to be summed, Column K contains the Year, and Column L contains the Quarter. Cell C2 and C3 represent which current and noncurrent amounts we are looking for and we hope that when we change this, it will update the values. For example, if we were trying to sum for all current amounts in Q3 2020, it will sum column I for all the following quarters: Q3 2020, Q4 2020, Q1 2021, Q2 2021, and Q3 2021. This sum will represent the current amounts. If were to change C3 to "4" we would hope it would sum the Q4 2020, Q1 2021, Q2 2021, Q3 2021, and Q4 2021 amounts. We hope that this current amount can be displayed in cell H2.

For the non-current amounts, it will equal Q4 2021 through Q4 2026 if C3 is "3". If C3 is "4", it will equal Q1 2022 through Q4 2026. We hope that this non-current amount can be displayed in cell H3.

Our data currently goes down to row 69.

Thank you again for all your help, it is truly appreciated.

 

[Fluff]

How about

=SUM(FILTER(I8:I100,(K8:K100&L8:L100>=C2&"Q"&C3)*(K8:K100&L8:L100<=C2+1&"Q"&C3)))
=SUM(FILTER(I8:I100,K8:K100&L8:L100>=C2+1&"Q"&C3+1))

 

[fchri31]

Hey Fluff,

Thank you for all your help before and now. So after using the formula, I am getting a total that seems to be incorrect. Please see attached picture (I filtered for 2020 and onwards) where I have highlighted yellow the amounts that would be considered current and highlighted orange the amounts that should be considered noncurrent based on Q3 2020 and totaled these two amounts. As you can see, it results in a different amount than what the formula is creating. Not quite sure if you know why, but as always, your helps is greatly appreciated.

 

[Fluff]

Can you post some sample data using the XL2BB add-in, it's impossible to test with an image.

 

[fchri31]

Playing around with it, It seems that it is pulling Q4 2021 into as non current, but it should be considered current!

 

[fchri31]

Here you are. I hope I did it right! Thanks!!

for fluff.xlsx
	B	C		E		G	H	I		K	L
2	Year	2020				Current DTS (USD)	256.72364	Yellow sum (Current)		$ 296.72
3	Quarter	3				Non Current DTS (USD)	225	Orange sum (Noncurrent)		$ 185
4								Red highlighted is irrelevant
5
6
7	Reporting Unit	Entity		Copyright Acquisitions		Currency	Payment Amount Functional Currency	Payment Amount (USD)		Payment Year	Payment Quarter
23	S	Entity		GG		GBP	£ 34	$ 44.39		2020	Q1
25	S	Entity		HH		GBP	£ 20	$ 26.11		2020	Q2
27	S	Entity		II		USD	$ 44	$ 44.00		2020	Q2
30	NS	Entity		JJ		USD	$ 10	$ 10.00		2020	Q1
31	NS	Entity		JJ		USD	$ 10	$ 10.00		2021	Q1
32	NS	Entity		JJ		USD	$ 10	$ 10		2022	Q1
33	NS	Entity		JJ		USD	$ 10	$ 10		2023	Q1
34	NS	Entity		JJ		USD	$ 10	$ 10		2024	Q1
35	NS	Entity		JJ		USD	$ 10	$ 10		2025	Q1
36	NS	Entity		JJ		USD	$ 15	$ 15		2026	Q1
39	NS	Entity		LL		USD	$ 25	$ 25.00		2020	Q3
41	NS	Entity		MM		USD	$ 30	$ 30.00		2020	Q2
42	NS	Entity		MM		USD	$ 40	$ 40.00		2020	Q2
44	S	Entity		NN		USD	$ 50	$ 50.00		2020	Q1
48	S	Entity		PP		GBP	£ 40	$ 52.22		2020	Q2
50	S	Entity		QQ		USD	$ 60	$ 60.00		2021	Q3
51	S	Entity		QQ		USD	$ 70	$ 70		2022	Q3
53	S	Entity		RR		USD	$ 43	$ 43.00		2021	Q2
55	S	Entity		SS		GBP	£ 22	$ 28.72		2021	Q1
57	NS	Entity		TT		USD	$ 10	$ 10.00		2020	Q2
58	NS	Entity		TT		USD	$ 10	$ 10.00		2020	Q3
59	NS	Entity		TT		USD	$ 10	$ 10.00		2020	Q4
60	NS	Entity		TT		USD	$ 10	$ 10.00		2021	Q1
61	NS	Entity		TT		USD	$ 10	$ 10.00		2021	Q2
62	NS	Entity		TT		USD	$ 10	$ 10.00		2021	Q3
63	NS	Entity		TT		USD	$ 10	$ 10.00		2021	Q4
64	NS	Entity		TT		USD	$ 20	$ 20		2022	Q1
66	S	Entity		UU		USD	$ 30	$ 30.00		2021	Q4
68	S	Entity		VV		USD	$ 40	$ 40.00		2021	Q3
69	S	Entity		VV		USD	$ 40	$ 40		2022	Q3
Current v Non Current

Cell Formulas
Range	Formula
H2	H2	=SUM(FILTER(I8:I100,(K8:K100&L8:L100>=C2&"Q"&C3)*(K8:K100&L8:L100<=C2+1&"Q"&C3)))
H3	H3	=SUM(FILTER(I8:I100,K8:K100&L8:L100>=C2+1&"Q"&C3+1))
K2	K2	=SUM(I31+I39+I50+I53+I55+I58+I59+I60+I61+I62+I63+I66+I68)
K3	K3	=SUM(I32:I36)+I51+I64+I69
I23	I23	=H23*O8
I25	I25	=H25*O8
I27,I68:I69,I64,I57:I62,I53,I50:I51,I44,I41:I42,I39,I30:I36	I27	=H27*1
I48	I48	=H48*O8
I55	I55	=H55*O8

 

[Fluff]

You said that if you had 2020 & 3 it should sum Q3 2020, Q4 2020, Q1 2021, Q2 2021, and Q3 2021
Which is what it is doing.

 

[fchri31]

Hi Fluff,

My apologies, you are correct. It was an issue on my end as to why I thought the formula wasn't working.

Thank you again for everything, you truly are a life saver and are the best!!!

 

[Fluff]

You're welcome & thanks for the feedback.