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

T

Thasnow

New Member
Joined
Nov 27, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 2024-11-27_12-36-39.jpg
    2024-11-27_12-36-39.jpg
    59.8 KB · Views: 7

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Something like this:
Book1
CDEFGHIJKL
1YEARPackageReplacement
22006Full2021n202520262027
32005No20Full101
42007FullNo200
52005Full
62005No
Sheet3
Cell Formulas
RangeFormula
J3:L4J3=COUNTIFS(C2:C6,J2:L2-G3,D2:D6,I3:I4,E2:E6,"")
Dynamic array formulas.
 
Upvote 0
Dear Cubist, thanks a lot.
n 20, could be changed to 21 or 22 (18 or 19) and then 2025, 2026 etc should remain as Years.
If you change to 21 it should be shown in the year 2026 and not anymore in 2025.
Hope you understand my thoughts, Thanks again.
 
Upvote 0
I think it's doing that already. If not, show an example where the formula didn't work. Use XL2BB to post your example so we don't have to re-create the data.
 
Upvote 0
Thanks again, I have just realized that I did not think to the end.

Attached is the requested XL2BB file, now slightly larger and with additional criteria.

There are two types of groups:

1) Package: Full or No

2) Condition: replaces yes or no, if yes with a specific year

If the package is Full or No and the condition is «replaced», then it should be 5 or 7 years later in the 2025 - 2030 list.

If the package is Full or No and the condition is «not replaced», then it should be 18 or 20 years later in the list 2025 - 2030, additionally all those that were before 2025 should also be at 2025.

Hope you understand my thoughts otherwise let me know

Thanks
241127 Testexcel.xlsx
CDEFGHIJKLMNOP
2YearPackageReplacedPackagenCondition202520262027202820292030
32006FULLFull5Replaced
42007FULLNo7Replaced
52007FULL2022Full18Not replaced
62010FULLNo20Not replaced
72012FULL
82004FULL
92002FULL
102006FULL
112008NO2024
122006FULL
132006FULL
142006FULL2021
152008NO
162007FULL
172007FULL
182007FULL2022
192007FULL
202015FULL
212008FULL
222006FULL
232003FULL
242006FULL
252012FULL
262004NO2019
272006NO
282006NO
292004NO
302006NO2021
312002NO
322007NO
332007NO
342005NO
352006FULL
362006FULL2021
372007NO
382006FULL
392008FULL2023
402006FULL
412004FULL
422002FULL
432005FULL2022
442003FULL
452007FULL2023
462003NO
472001FULL
482003FULL
492002NO2023
502006FULL
512006NO2022
522006FULL2021
Sheet1 (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:E52Expression=CR3=0textNO
D3:D52Expression=ER3=0textNO
C3:C52Expression=FE3=0textNO
 
Upvote 0
Thasnow said:
If the package is Full or No and the condition is «replaced», then it should be 5 or 7 years later in the 2025 - 2030 list.
Click to expand...
Is this based on the "Replace" column?

Thasnow said:
If the package is Full or No and the condition is «not replaced», then it should be 18 or 20 years later in the list 2025 - 2030, additionally all those that were before 2025 should also be at 2025.
Click to expand...
Is this based on the "YEAR" column?
 
Upvote 0
"C" Year is given, "D" Package is given, "E" replaced with a date when it was done is given
Is this based on the "Replace" column? -> Correct
Is this based on the "YEAR" column? -> Correct
 
Upvote 0
See if this works:
Book1
CDEFGHIJKLMNO
2YearPackageReplacedPackagenCondition202520262027202820292030
32006FULLFULL5Replaced033200
42007FULLNO7Replaced010111
52007FULL2022FULL18Not replaced2210102
62010FULLNO20Not replaced423100
72012FULL
82004FULL
92002FULL
102006FULL
112008NO2024
122006FULL
132006FULL
142006FULL2021
152008NO
162007FULL
172007FULL
182007FULL2022
192007FULL
202015FULL
212008FULL
222006FULL
232003FULL
242006FULL
252012FULL
262004NO2019
272006NO
282006NO
292004NO
302006NO2021
312002NO
322007NO
332007NO
342005NO
352006FULL
362006FULL2021
372007NO
382006FULL
392008FULL2023
402006FULL
412004FULL
422002FULL
432005FULL2022
442003FULL
452007FULL2023
462003NO
472001FULL
482003FULL
492002NO2023
502006FULL
512006NO2022
522006FULL2021
Sheet5
Cell Formulas
RangeFormula
J3:O6J3=COUNTIFS($D:$D,$G3,IF($I3="Replaced",$E:$E,$C:$C),"<="&J$2-$H3,$E:$E,IF($I3="Not replaced","","<>"))-SUM($I3:I3)
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet5!$C$2:$E$52J3:O6
 
Upvote 0
WOW, thanks a lot, looks like it works.. Will still do some verification before I close it but hey thanks a lot
 
Upvote 0
So, I'm again in another position that can't help myself:
Given, the years where it should count the result (2025 to 2030) of Package Full, No and empty cell
Full is 20 years, means Year 2006 + 20 years = 2026
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, means 2025 - 10 years = 2015
"C" Year is given, "D" Package is given
Hope you can follow my thoughts otherwise let me know.
Thanks

241127 Testexcel.xlsx
CDEFGHIJKLM
2YearPackagePackagen202520262027202820292030
32006FULLFull20
42016No15
52007FULL10
62010FULL
72011FULL
82004FULL
92002FULL
102006FULL
112018
122006FULL
132006FULL
142011FULL
152008NO
162007FULL
172007FULL
182020
192007FULL
202015NO
212008FULL
222006FULL
232003FULL
242006FULL
252012FULL
262014NO
272016NO
282017
292004NO
302015
312002NO
322007NO
332011NO
342005NO
352006FULL
362006FULL
372012NO
382006FULL
392008FULL
402006FULL
412004FULL
422002FULL
432005FULL
442003FULL
452007FULL
462003NO
472001FULL
482003FULL
492002NO
502016
512006NO
522006FULL
Sheet1 (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D52Expression=EO3=0textNO
C3:C52Expression=FB3=0textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
981
ahaye
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
C
  • Question Question
How do you count occurances of something over a period of time?
Replies
1
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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