How do you proceed with an Excel list that contains several criteria, a specific time period and a count for different years?

[Thasnow]

Hello all,

I am not so familiar with this type of Excel function:

Attached you will find a test Excel.

Given are the year, package, replacement
I would like to know in the year 2025, 2026, 2027 and 2028
How many of the packages Full and No are available if they are exactly 20 years old at that time (G3).
If they are replaced, they should not be counted as part of the 2025, 2026, 2027 and 2028 package.

Thanks a lot for your help
Ben

 

[Cubist]

Similar to before.

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	Year	Package		Package	n	2025	2026	2027	2028	2029	2030
2	2006	FULL		Full	20	9	11	5	2	0	1
3	2016			No	15	8	1	1	0	1	1
4	2007	FULL			10	1	2	1	1	0	1
5	2010	FULL
6	2011	FULL
7	2004	FULL
8	2002	FULL
Sheet1

Cell Formulas
Range	Formula
H2:M4	H2	=COUNTIFS($D$2:$D$51,IF($F2="","",$F2),$C$2:$C$51,"<="&H$1-$G2)-SUM($G2:G2)+$G2

 

[Thasnow]

Dear Cubist, thanks a lot, it is similar, but difference should be by Full is only for that year example 2025 - n (20) means count all that is only given in 2005 and not before.
I tried to change in the formula "<=" to "=". As soon as I change that to the other years, I get all the data wrong due to the SUM formula.

 

[Cubist]

How about:

Book1
	C	D	E	F	G	H	I	J	K	L	M
1	Year	Package		Package	n	2025	2026	2027	2028	2029	2030
2	2006	FULL		Full	20	1	11	5	2	0	1
3	2016			No	15	8	1	1	0	1	1
4	2007	FULL			10	1	2	1	1	0	1
5	2010	FULL
6	2011	FULL
7	2004	FULL
8	2002	FULL
9	2006	FULL
10	2018
11	2006	FULL
12	2006	FULL
13	2011	FULL
14	2008	NO
15	2007	FULL
16	2007	FULL
17	2020
18	2007	FULL
19	2015	NO
20	2008	FULL
21	2006	FULL
22	2003	FULL
23	2006	FULL
24	2012	FULL
25	2014	NO
26	2016	NO
27	2017
28	2004	NO
29	2015
30	2002	NO
31	2007	NO
32	2011	NO
33	2005	NO
34	2006	FULL
35	2006	FULL
36	2012	NO
37	2006	FULL
38	2008	FULL
39	2006	FULL
40	2004	FULL
41	2002	FULL
42	2005	FULL
43	2003	FULL
44	2007	FULL
45	2003	NO
46	2001	FULL
47	2003	FULL
48	2002	NO
49	2016
50	2006	NO
51	2006	FULL
Sheet8

Cell Formulas
Range	Formula
H2:M4	H2	=COUNTIFS($D$2:$D$51,IF($F2="","",$F2),$C$2:$C$51,IF($F2="Full","=","<=")&H$1-$G2)+($F2<>"Full")*(-SUM($G2:G2)+$G2)

 

[Thasnow]

Looks great thank you, but now getting to the next level:

Given, the years where it should counts the results (2025 to 2030) of Package Full, No and empty cell
Full is 20 years, means Year "C" 2005 + 20 years = 2025 (Full is only for that year example 2025 - n (20) means count all that is only given in 2005 and not before)
No is 15 years, means 2025 - 15 years = 2010 or below (could be 2009, 2008 etc) but all count in 2025. 2026 will be then -15 years =2011
Empty cell is 10 years, "C" 2015 + 10 years = 2025
"C" Year is given, "D" Package is given, "E" Replaced is given, "F" Replaced by is given

NEW:
Replaced, yes or no in Full, No or empty cell

Yes can be Yes ABT or YES OKB both are same Yes
No can be No MBT or No AZU both are same No
empty cell = empty cell

By Full, the "replaced" Yes or No is just for counting (example Yes = 4 / No = 5) no additional need
By No, the replaced Yes or No is different!
- No and replaced yes = the year on F +n (15), example C23 = 2012 + n15 = count +1 in cell 2027
- No and replaced no = the year on C +n (15), example C23 = 2015 + n15 = count +1 in cell 2030
Empty cell just count in the empty cell

Hope you can follow my thoughts otherwise let me know.

Test.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
5	Year	Package	Replaced	Replaced by		Package	n	Replaced	2025	2026	2027	2028	2029	2030	2031	2032
6	2006	FULL	Yes ABT	2020		Full	20	Yes
7	2016					Full	20	No
8	2007	FULL	Yes ABT	2021		No	15	Yes
9	2010	FULL	Yes ABT	2021		No	15	No
10	2011	FULL	No AZU	not yet			10	Yes
11	2004	FULL	No MBT	not yet			10	No
12	2002	FULL	No MBT	not yet
13	2006	FULL	Yes OKB	2014
14	2018
15	2006	FULL	No AZU	not yet
16	2006	FULL	No AZU	not yet
17	2011	FULL	Yes OKB	2021
18	2015	NO	No MBT	not yet
19	2007	FULL	No AZU	not yet
20	2007	FULL	Yes OKB	2022
21	2020
22	2007	FULL	No MBT	not yet
23	2014	NO	Yes ABT	2012
24	2008	FULL	No MBT	not yet
25	2006	FULL	No AZU	not yet
26	2003	FULL	Yes ABT	2020
27	2006	FULL	Yes ABT	2021
28	2012	FULL	Yes OKB	2021
29	2014	NO	Yes OKB	2024
30	2016	NO	Yes OKB	2023
31	2017
32	2014	NO	No MBT	not yet
33	2015
34	2011	NO	No AZU	not yet
35	2007	NO	No MBT	not yet
36	2011	NO	No MBT	not yet
37	2005	NO	Yes ABT	2023
38	2006	FULL	Yes ABT	2020
39	2006	FULL	Yes OKB	2021
40	2012	NO	No AZU	not yet
41	2006	FULL	No MBT	not yet
42	2008	FULL	No AZU	not yet
43	2006	FULL	Yes OKB	2020
44	2004	FULL	No AZU	not yet
45	2002	FULL	No MBT	not yet
46	2005	FULL	Yes ABT	2021
47	2003	FULL	No AZU	not yet
48	2007	FULL	No MBT	not yet
49	2003	NO	No MBT	not yet
50	2001	FULL	Yes ABT	2010
51	2003	FULL	No AZU	not yet
52	2002	NO	Yes ABT	2020
53	2016
54	2006	NO	No AZU	not yet
55	2006	FULL	Yes OKB	2014
Test 4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:F55	Expression	=FE6=0	text	NO
D6:D55	Expression	=EP6=0	text	NO
C6:C55	Expression	=FC6=0	text	NO

 

[Cubist]

Please present the problem in its entirety. It's not productive to introduce new piece of information every time I gave you a solution.

 

[Thasnow]

I agree and I didn't get all the information likewise. This is the last version with all the criteria that are given. Thanks anyway