Count consecutive Dates

D

Dannicw77

New Member
Joined
Apr 24, 2023
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi. I 'm looking to show per attendee, the number of consecutive dates per period. I have tried this from another thread but I keep getting 1 as my result... Any help?

=IF(AND(A3=A2,B3=B2,C3=C2,D3=(D2+1)),"",ROW(E2)-IFERROR(MATCH(1e+100,D$1:D1),1))

Child Details Full NamePurchaser Customer ReferencePeriodStart Date
Aadesh TolaniCUS-3261-BIZFeb Half Term
15/02/2023​
Aadesh TolaniCUS-3261-BIZFeb Half Term
16/02/2023​
Aadesh TolaniCUS-3261-BIZEaster
05/04/2023​
Aarav PatelCUS-8382-JYNOct Half Term
24/10/2022​
Aarav PatelCUS-8382-JYNOct Half Term
25/10/2022​
Aarav PatelCUS-8382-JYNOct Half Term
26/10/2022​
Aarav PatelCUS-8382-JYNOct Half Term
27/10/2022​
Aarav PatelCUS-8382-JYNOct Half Term
28/10/2022​
Aarav PatelCUS-8382-JYNFeb Half Term
13/02/2023​
Aarav PatelCUS-8382-JYNFeb Half Term
14/02/2023​
Aarav PatelCUS-8382-JYNFeb Half Term
15/02/2023​
Aarav PatelCUS-8382-JYNFeb Half Term
16/02/2023​
Aarav PatelCUS-8382-JYNFeb Half Term
17/02/2023​
Aariv RaoCUS-8356-WQWOct Half Term
25/10/2022​
Aariv RaoCUS-8356-WQWOct Half Term
26/10/2022​
Aariv RaoCUS-8356-WQWOct Half Term
27/10/2022​
Aarlo DalyCUS-2342-SDHEaster
05/04/2023​
Aarlo DalyCUS-2342-SDHEaster
06/04/2023​
Aarlo DalyCUS-2342-SDHEaster
12/04/2023​
Aarlo DalyCUS-2342-SDHEaster
13/04/2023​
Aaron BarkerCUS-5358-ODAFeb Half Term
14/02/2023​
Aaron BarkerCUS-5358-ODAFeb Half Term
15/02/2023​
Aaron BarkerCUS-5358-ODAFeb Half Term
16/02/2023​
Aaron BarkerCUS-5358-ODAEaster
12/04/2023​
Aaron LawrenceCUS-6214-EMREaster
12/04/2023​
Aaron LawrenceCUS-6214-EMREaster
13/04/2023​
Aaron LawrenceCUS-6214-EMREaster
14/04/2023​
Aaron MosesCUS-4434-NUDFeb Half Term
14/02/2023​
Aaron MosesCUS-4434-NUDFeb Half Term
15/02/2023​
Aaryaa KrishnmurthyCUS-4847-MKSOct Half Term
25/10/2022​
Aaryaa KrishnmurthyCUS-4847-MKSOct Half Term
26/10/2022​
Aaryaa KrishnmurthyCUS-4847-MKSXmas
19/12/2022​
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Fluff.xlsm
ABCDE
1Child Details Full NamePurchaser Customer ReferencePeriodStart DateCount
2Aadesh TolaniCUS-3261-BIZFeb Half Term15/02/2023 
3Aadesh TolaniCUS-3261-BIZFeb Half Term16/02/20232
4Aadesh TolaniCUS-3261-BIZEaster04/05/20231
5Aarav PatelCUS-8382-JYNOct Half Term24/10/2022 
6Aarav PatelCUS-8382-JYNOct Half Term25/10/2022 
7Aarav PatelCUS-8382-JYNOct Half Term26/10/2022 
8Aarav PatelCUS-8382-JYNOct Half Term27/10/2022 
9Aarav PatelCUS-8382-JYNOct Half Term28/10/20225
10Aarav PatelCUS-8382-JYNFeb Half Term13/02/2023 
11Aarav PatelCUS-8382-JYNFeb Half Term14/02/2023 
12Aarav PatelCUS-8382-JYNFeb Half Term15/02/2023 
13Aarav PatelCUS-8382-JYNFeb Half Term16/02/2023 
14Aarav PatelCUS-8382-JYNFeb Half Term17/02/20235
15Aariv RaoCUS-8356-WQWOct Half Term25/10/2022 
16Aariv RaoCUS-8356-WQWOct Half Term26/10/2022 
17Aariv RaoCUS-8356-WQWOct Half Term27/10/20223
18Aarlo DalyCUS-2342-SDHEaster05/04/2023 
19Aarlo DalyCUS-2342-SDHEaster06/04/20232
20Aarlo DalyCUS-2342-SDHEaster12/04/2023 
21Aarlo DalyCUS-2342-SDHEaster13/04/20232
22Aaron BarkerCUS-5358-ODAFeb Half Term14/02/2023 
23Aaron BarkerCUS-5358-ODAFeb Half Term15/02/2023 
24Aaron BarkerCUS-5358-ODAFeb Half Term16/02/20233
25Aaron BarkerCUS-5358-ODAEaster12/04/20231
26Aaron LawrenceCUS-6214-EMREaster12/04/2023 
27Aaron LawrenceCUS-6214-EMREaster13/04/2023 
28Aaron LawrenceCUS-6214-EMREaster14/04/20233
29Aaron MosesCUS-4434-NUDFeb Half Term14/02/2023 
30Aaron MosesCUS-4434-NUDFeb Half Term15/02/20232
31Aaryaa KrishnmurthyCUS-4847-MKSOct Half Term25/10/2022 
32Aaryaa KrishnmurthyCUS-4847-MKSOct Half Term26/10/20222
33Aaryaa KrishnmurthyCUS-4847-MKSXmas19/12/20221
Main
Cell Formulas
RangeFormula
E2:E33E2=IF(AND(A3=A2,B3=B2,C3=C2,D3=(D2+1)),"",ROW()-LOOKUP(2,1/(E$1:E1<>""),ROW(E$1:E1)))
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,477
Messages
6,125,036
Members
449,205
Latest member
Eggy66

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