How to create a dynamic rolling count by year column

J

jdavis51198

New Member
Joined
Nov 4, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to do a few things based on the below sheet. Essentially I have a long sheet of dates broken down by year ,month ,day hour...etc. I aim to have a rolling count of the total number of rows for each year. So column "percentcounter is an example of what I would expect to see. It will count right up until 2026, and then it will reset back to one until the first occurrence of 2027 happens. What would be the best way to go about this, I have tried using a count statement like such =IF(B3=2025,COUNT($G$2:G3), but I am not sure how to tell it to "reset" at the first occurrence of the change in a year.









SensitivityAnalysis v7.xlsx
ABCDEFGHIJK
1* MISO 2021 Q3IFERRO(AVERAGEIFS(hourlyprices,asOfDate,A3,HE,C3),0)* SNL Forward LMP MISO 2022 Q3.csv
2sim.asOfDatesim.ContractYearsim.HEsim.FOMTimeOfUsegenerationF(0,T)floornewFloorpercentCounterpercentageStrike
312/31/20252025112/1/2025OffPeak042.050-2213%
412/31/20252025212/1/2025OffPeak041.010-2223%
512/31/20252025312/1/2025OffPeak040.890-2233%
612/31/20252025412/1/2025OffPeak041.010-2243%
712/31/20252025512/1/2025OffPeak041.130-2253%
812/31/20252025612/1/2025OffPeak043.620-2263%
912/31/20252025712/1/2025Peak053.140-2273%
1012/31/20252025812/1/2025Peak055.920-2283%
1112/31/20252025912/1/2025Peak153.960-2293%
1212/31/202520251012/1/2025Peak651.930-22103%
1312/31/202520251112/1/2025Peak1251.780-22113%
1412/31/202520251212/1/2025Peak1348.640-22123%
1512/31/202520251312/1/2025Peak1147.080-22133%
1612/31/202520251412/1/2025Peak946.870-22143%
1712/31/202520251512/1/2025Peak743.830-22153%
1812/31/202520251612/1/2025Peak643.190-22163%
1912/31/202520251712/1/2025Peak044.850-22173%
2012/31/202520251812/1/2025Peak062.260-22183%
2112/31/202520251912/1/2025Peak058.060-22193%
2212/31/202520252012/1/2025Peak058.350-22203%
2312/31/202520252112/1/2025Peak057.080-22213%
2412/31/202520252212/1/2025Peak053.360-22223%
2512/31/202520252312/1/2025OffPeak042.880-22233%
2612/31/202520252412/1/2025OffPeak040.730-22243%
271/1/2026202611/1/2026OffPeak044.020-2213%
281/1/2026202621/1/2026OffPeak039.480-2223%
291/1/2026202631/1/2026OffPeak037.230-2233%
301/1/2026202641/1/2026OffPeak037.260-2243%
311/1/2026202651/1/2026OffPeak039.080-2253%
321/1/2026202661/1/2026OffPeak040.000-2263%
331/1/2026202671/1/2026OffPeak045.260-2273%
341/1/2026202681/1/2026OffPeak252.310-2283%
351/1/2026202691/1/2026OffPeak553.580-2293%
VWHSP (Hour)
Cell Formulas
RangeFormula
J3:J26J3=IF(B3=2025,COUNT($G$2:G3),COUNTIF($G$2:G3,"=2026"))
J27:J35J27=IF(B27=2026,COUNT($G$26:G26))
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Welcome to the MrExcel board!

Try this in J3 and just copy it down the column.
Excel Formula: 
=COUNTIF(B$3:B3,B3)
 
Upvote 0
Solution
Hi J,

See last column

Book1
ABCDEFGHIJKLM
2sim.asOfDatesim.ContractYearsim.HEsim.FOMTimeOfUsegenerationF(0,T)floornewFloorpercentCounterpercentageStrike2021-12-301
312/31/20252025112/1/2025OffPeak042.050-2210.032021-12-312
412/31/20252025212/1/2025OffPeak041.010-2220.032021-12-312
512/31/20252025312/1/2025OffPeak040.890-2230.032021-12-312
612/31/20252025412/1/2025OffPeak041.010-2240.032021-12-312
712/31/20252025512/1/2025OffPeak041.130-2250.032021-12-312
812/31/20252025612/1/2025OffPeak043.620-2260.032022-12-313
912/31/20252025712/1/2025Peak053.140-2270.032022-12-313
1012/31/20252025812/1/2025Peak055.920-2280.032022-12-313
1112/31/20252025912/1/2025Peak0.83021953.960-2290.032022-12-313
1212/31/202520251012/1/2025Peak6.44491451.930-22100.032022-12-313
1312/31/202520251112/1/2025Peak12.2960551.780-22110.032023-12-314
1412/31/202520251212/1/2025Peak12.9085748.640-22120.032023-12-314
1512/31/202520251312/1/2025Peak11.2720447.080-22130.032023-12-314
1612/31/202520251412/1/2025Peak8.83129546.870-22140.032023-12-314
1712/31/202520251512/1/2025Peak7.43189743.830-22150.032023-12-314
1812/31/202520251612/1/2025Peak6.16020843.190-22160.032024-12-315
1912/31/202520251712/1/2025Peak044.850-22170.032024-12-315
2012/31/202520251812/1/2025Peak062.260-22180.032024-12-315
2112/31/202520251912/1/2025Peak058.060-22190.032024-12-315
2212/31/202520252012/1/2025Peak058.350-22200.032024-12-315
2312/31/202520252112/1/2025Peak057.080-22210.032025-12-316
2412/31/202520252212/1/2025Peak053.360-22220.032025-12-316
2512/31/202520252312/1/2025OffPeak042.880-22230.032025-12-316
2612/31/202520252412/1/2025OffPeak040.730-22240.032025-12-316
271/1/2026202611/1/2026OffPeak044.020-2210.032025-12-316
281/1/2026202621/1/2026OffPeak039.480-2220.032026-12-317
291/1/2026202631/1/2026OffPeak037.230-2230.032026-12-317
301/1/2026202641/1/2026OffPeak037.260-2240.032026-12-317
311/1/2026202651/1/2026OffPeak039.080-2250.032026-12-317
321/1/2026202661/1/2026OffPeak0400-2260.032026-12-317
331/1/2026202671/1/2026OffPeak045.260-2270.032027-12-318
341/1/2026202681/1/2026OffPeak1.60177352.310-2280.032027-12-318
351/1/2026202691/1/2026OffPeak5.3322553.580-2290.032027-12-318
Sheet1
Cell Formulas
RangeFormula
M3:M35M3=IF(L3=L2,M2,M2+1)
L8:L35L8=EDATE(L3,12)
J3:J26J3=IF(B3=2025,COUNT($G$2:G3),COUNTIF($G$2:G3,"=2026"))
J27:J35J27=IF(B27=2026,COUNT($G$26:G26))
 
Upvote 0
Thank you very much; that was what I was after; however, I realized I might need to make a minor tweak, and I am curious if you had any advice. I am not looking to get the total cumulative count in each cell, so for example, 2025 would have x number of values, and I would use that number for every row as the denominator for the year 2025. Then in 2026, it would count every value in 2026 and use that as the denominator in my calculation, the numerator being =COUNTIFS(B$3:B3,B3,G$3:G3,"<0"), so the goal is seeing the rolling percentage of negative prices. Any help would be great!
 
Upvote 0
What about some new sample data and expected results and explain again in relation to that sample data?
BTW, you can hide irrelevant columns (or rows) before clicking Mini Sheet to keep the Mini Sheet a better size for viewing in the forum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
918
JEC
JEC
R
  • Question
Help!
Replies
3
Views
1K
63falcondude
63falcondude

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

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