Formula to consolidate Data and remove entries with no data in that year

[Newbie73]

Hello,

I need help creating a formula that would consolidate results, as in keeping the same ID for a year a Sum the numbers for that year while also at the same time, if that ID hasn't had a value in that year, to be removed from the filter/list.

For example the data:

PT1000000 - 1 - 2012
PT1000000 - 1 - 2013
PT1000000 - 2 - 2014
PT2000000 - 3 - 2013
PT2000000 -1 - 2014
PT2000000 - 1 - 2015

Result in column 2013 then consolidating the data and sum the previous result:

P100000 - 2
PT20000 - 3

2014

P100000- 4
PT20000 - 4

2015 (PT10000 was removed from 2015 list as it doesn't have any result in this year, it it had an entry with the year with a number even if it's zero it would still count and be in 2015)

PT20000 - 5


Example spreadsheet with expected results and a few more entries:

test3.xlsx

Thank you in advance!

 

[etaf]

you could use
=UNIQUE(FILTER($A$1:$A$12,$D$1:$D$12=H$1))

to ge the PT numbers

I'm not sure what you are doing
is this a sum of the number grwate and = to the PT id
for example
2013
PT 1 = 3

not following that calculation , based on examples

test3(Sheet1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	PT100000	1		2012				2012			2013			2014			2015			2016			2017
2	PT100000	1		2014				PT100000	1		PT100000	3		PT100000	4		PT200000	6		PT200000	7		PT200000	8
3	PT100000	2		2013							PT200000	2		PT200000	5		PT300000	2		PT300000	3
4	PT200000	3		2014
5	PT200000	1		2016					4
6	PT200000	1		2015
7	PT200000	2		2013
8	PT200000	1		2017
9	PT300000	1		2015
10	PT300000	1		2016
11	PT300000	1		2015
12	PT200000	0		2014
test3(Sheet1)

Cell Formulas
Range	Formula
H2,W2,T2:T3,Q2:Q3,N2:N3,K2:K3	H2	=UNIQUE(FILTER($A$1:$A$12,$D$1:$D$12=H$1))
I5	I5	=SUMIFS($B$1:$B$12,$D$1:$D$12,">="&H$1,$A$1:$A$12,H2)

 

[Newbie73]

Hello etaf and thanks for your reply! In the calculations is a sum of column B from that same ID, from all the entries of that year or all the previous years (some times the same ID might also have several entries within the same year)

For example, 2013 L2 equals 3, because PT100000 "1" in 2012 (row 1) and row 3 PT100000 "2" in 2013 bringing the total of 3 for ID PT100000 in 2013

Another example, 2015 - R2 equals 6

B4, B6, B7 are entries from the same ID, PT200000 from 2015 and previous years, sum equals 6

Hope this helps, let me know if not and I can explain in a different way

 

[hagia_sofia]

Hello, here is an option:

=LET(
ID,$B$2:$B$13,
count,$C$2:$C$13,
years,$E$2:$E$13,
year,I2,
a,UNIQUE(FILTER(ID,years=year)),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(count,(ID=y)*(years<=year)))))),1),
HSTACK(a,b))

 

[etaf]

just for completeness of my version
=SUMIFS($B$2:$B$13,$D$2:$D$13,"<="&H$1,$A$2:$A$13,H2)
you could add add
=IF(W2="","",SUMIFS($B$2:$B$13,$D$2:$D$13,"<="&W$1,$A$2:$A$13,W2))

but the LET by

hagia_sofia​

doing it all in one go is probably better, as you have 365 version

test3(Sheet1).xlsx
	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
1								2012			2013			2014			2015			2016			2017
2	PT100000	1		2012				PT100000	1		PT100000	3		PT100000	4		PT200000	6		PT200000	7		PT200000	8
3	PT100000	1		2014							PT200000	2		PT200000	5		PT300000	2		PT300000	3
4	PT100000	2		2013
5	PT200000	3		2014
6	PT200000	1		2016
7	PT200000	1		2015
8	PT200000	2		2013
9	PT200000	1		2017
10	PT300000	1		2015
11	PT300000	1		2016
12	PT300000	1		2015
13	PT200000	0		2014
14
15
16
17
test3(Sheet1)

Cell Formulas
Range	Formula
H2,W2,T2:T3,Q2:Q3,N2:N3,K2:K3	H2	=UNIQUE(FILTER($A$2:$A$13,$D$2:$D$13=H$1))
I2:I16,X2:X16,U2:U16,R2:R16,O2:O16,L2:L16	I2	=IF(H2="","",SUMIFS($B$2:$B$13,$D$2:$D$13,"<="&H$1,$A$2:$A$13,H2))

 

[Newbie73]

Thank you both! Works as intended

 

[Newbie73]

If I may asked one more thing, I'm using your formula Hagia_sofia as it does both things automatically. How would I change the formula to also do the exact same thing on Column C values? (it was empty on the example spreadsheet, added some numbers now as I forgot they were also needed)

 

[hagia_sofia]

If I may asked one more thing, I'm using your formula Hagia_sofia as it does both things automatically. How would I change the formula to also do the exact same thing on Column C values? (it was empty on the example spreadsheet, added some numbers now as I forgot they were also needed)

So, the result would consist of 3 columns where the 3rd would contain the same calculation as the 2nd or something else?

 

[Newbie73]

Exactly the same yes, I've just updated the example sheet if it helps, sorry about it!

 

[hagia_sofia]

So, this?

=LET(
ID,$B$2:$B$13,
count_1,$C$2:$C$13,
count_2,$D$2:$D$13,
years,$E$2:$E$13,
year,I2,
a,UNIQUE(FILTER(ID,years=year)),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(count_1,(ID=y)*(years<=year)))))),1),
c,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(count_2,(ID=y)*(years<=year)))))),1),
HSTACK(a,b,c))