# Identify equipment used -No of consecutive days

#### ABC1

##### New Member
Hello,
I appreciate your time and effort.

I would like to have formula for column O and P.
Column A : lots of Asset
Column C: date
If one Asset is used more than 5 consecutive days than that date range (Start date and end date) should go to column 0 and no of consecutive days go to Column P.
I try no of different formula
Column E : =IF(C4-C3=1,C4-C3+E3,1)
Column F: =IF(E4+1=E5, "In used","End")
Column G: =IF(A4=A5,IF(C4-C3=1,C4-C3+G3,1),C4-C3+G3)
Column H: =IF(A4<>A3,"START OUTTER",IF(A4=A5,"Used","Outer End"))
How can I copy Column C and (E or G) to column O and P base on my condition.
Also would like to update status as "consider as day use- if no of days used less than 5 days -and this total no need to copy Column Q.
Thanks,

##### Well-known Member
Hi ABC1,

You can't use the AGGREGATE SUM option 9 when specifying ROW numbers for AGGREGATE. i.e. the option 9 requires the array specified such as =AGGREGATE(9,6,\$D\$2:\$D\$5) which would return 0+4+4+4=12

I suspect you are trying to sum the column D hours for the Asset specified in N3 for the dates specified in P3.

If you can fix that date in column C (please!) and let me split column P into two columns, start date and end date, then it becomes a simple SUMIFS function. As it is we can use that column P formula to find the start and end rows for a SUM.
NOTE: The #REF errors are because those rows are >25.

ABC1.xlsx
MNOPQR
2Last RowAsset #DescriptionUnbroken Use PeriodCount of Continuous Days UseTotal No. of Hours based on continued use
36113Van Helsing1/1/2022 - 5/1/2022520
416113Van Helsing7/1/2022 - 16/1/20221040
521101Van Halen2/2/2022 - 6/2/2022520
641101Van Halen9/2/2022 - 26/2/202218#REF!
753101Van Halen3/3/2022 - 10/3/20228#REF!
4th
Cell Formulas
RangeFormula
M3:M7M3=IFERROR(AGGREGATE(15,6,ROW(\$G\$2:\$G\$9998)-ROW(\$G\$1)/((\$F\$2:\$F\$9998="End")*(\$E\$2:\$E\$9998>=5)),ROW()-ROW(\$M\$2)),"")
N3:O7N3=IF(\$M3="","",INDEX(A\$2:A\$9998,\$M3))
P3:P7P3=IF(\$M3="","",TEXT(INDEX(\$C\$2:\$C\$9998,AGGREGATE(14,6,ROW(\$C\$2:\$C\$9998)-ROW(\$C\$1)/((\$A\$2:\$A\$9998=\$N3)*(\$E\$2:\$E\$9998=1)*(ROW(\$C\$2:\$C\$9998)<\$M3)),1)),"d/m/yyyy")&" - "&TEXT(INDEX(\$C\$2:\$C\$9998,\$M3),"d/m/yyyy"))
Q3:Q7Q3=IF(\$M3="","",INDEX(\$E\$2:\$E\$9998,\$M3))
R3:R7R3=IF(\$M3="","",SUM(INDEX(\$D\$2:\$D\$25,AGGREGATE(14,6,ROW(\$C\$2:\$C\$9998)-ROW(\$C\$1)/((\$A\$2:\$A\$9998=\$N3)*(\$E\$2:\$E\$9998=1)*(ROW(\$C\$2:\$C\$9998)<\$M3)),1)):INDEX(\$D\$2:\$D\$25,M3)))

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### ABC1

##### New Member
I did start from fresh with Asset as number and date is in date format. there is one more column E (total \$) is for Total.
Now we can split p column into two and then use sumif function
Total no of hrs based on continue used - mine is not coming correct way

=IF(\$M3="","",SUM(INDEX(\$D\$2:\$D\$6000,AGGREGATE(14,6,ROW(\$C\$2:\$C\$6000)-ROW(\$C\$1)/((\$A\$2:\$A\$6000=\$O3)*(\$F\$2:\$F\$6000=1)*(ROW(\$C\$2:\$C\$6000)<\$M3)),1)),INDEX(\$D\$2:\$D\$6000,M3)))

Thanks

##### Well-known Member
OK, I've split the range into two columns and the calculations become easier with real date fields.

ABC1.xlsx
ABCDEFGHIJKLMNOPQRSTU
1AssetEquipmentDateSum of SomethingTotal \$No of Consecutive DaysStatus
2884Car Ladelavigne1-Jan-20205.541EndActual Last RowLast RowAsset #DescriptionUnbroken Use Period StartUnbroken Use Period EndCount of Continuous Days UseTotal No. of Hours based on continued useCharges
3113Van Helsing1-Jan-202012101In used76113Van Helsing1-Jan-20205-Jan-202056050
4113Van Helsing2-Jan-202012102In used1716113Van Helsing7-Jan-202016-Jan-202010120100
5113Van Helsing3-Jan-202012103In used2221101Van Halen2-Feb-20206-Feb-202056050
6113Van Helsing4-Jan-202012104In used4241101Van Halen9-Feb-202026-Feb-202018216180
8113Van Helsing7-Jan-202012101In used
9113Van Helsing8-Jan-202012102In used
10113Van Helsing9-Jan-202012103In used
11113Van Helsing10-Jan-202012104In used
12113Van Helsing11-Jan-202012105In used
5th
Cell Formulas
RangeFormula
F2:F12F2=IF(A2="","",IF(AND(A2=A1,IFERROR(VALUE(C2)=VALUE(C1)+1,0)),SUM(F1)+1,1))
G2:G12G2=IF(A2="","",IF(F2+1=F3,"In used","End"))
M3:M12M3=IF(\$N3="","",N3+ROW(\$A\$1))
N3:N12N3=IFERROR(AGGREGATE(15,6,ROW(\$H\$2:\$H\$99999)-ROW(\$H\$1)/((\$G\$2:\$G\$99999="End")*(\$F\$2:\$F\$99999>=5)),ROW()-ROW(\$N\$2)),"")
O3:P12O3=IF(\$N3="","",INDEX(A\$2:A\$99999,\$N3))
Q3:Q12Q3=IF(\$N3="","",INDEX(\$C\$2:\$C\$99999,AGGREGATE(14,6,ROW(\$C\$2:\$C\$99999)-ROW(\$C\$1)/((\$A\$2:\$A\$99999=\$O3)*(\$F\$2:\$F\$99999=1)*(ROW(\$C\$2:\$C\$99999)<\$N3)),1)))
R3:R12R3=IF(\$N3="","",INDEX(\$C\$2:\$C\$99999,\$N3))
S3:S12S3=IF(\$N3="","",INDEX(\$F\$2:\$F\$99999,\$N3))
T3:T12T3=IF(\$N3="","",SUMIFS(\$D\$2:\$D\$99999,\$A\$2:\$A\$99999,O3,\$C\$2:\$C\$99999,">="&Q3,\$C\$2:\$C\$99999,"<="&R3))
U3:U12U3=IF(\$N3="","",SUMIFS(\$E\$2:\$E\$99999,\$A\$2:\$A\$99999,O3,\$C\$2:\$C\$99999,">="&Q3,\$C\$2:\$C\$99999,"<="&R3))

#### ABC1

##### New Member
Ok Thanks lots. I will work on it

#### ABC1

##### New Member

Thanks lots. Its worked. Again thanks

Formula is ready but I need to update that formula to apply other cell.

##### Well-known Member
Thanks lots. Its worked. Again thanks

Formula is ready but I need to update that formula to apply other cell.
You're welcome.
If it's a new question then you should start a new thread so that others can also contribute.

#### ABC1

##### New Member

Replies
2
Views
279
Replies
1
Views
61
Replies
3
Views
122
Replies
17
Views
356
Replies
0
Views
183

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,578
Messages
5,765,205
Members
425,266
Latest member
bishopc22

### 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.

### Which adblocker are you using?

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

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