Table Information

[hemrajrav328]

which formula can I use to map selected information from table for "Idle Time" only when the duration is greater than 0:05:00 for selected name.

[HansV]

See the attached version.

Table.xlsx

[hemrajrav328]

Thanks. Works Great

[hemrajrav328]

What's this type of formula called ?

[HansV]

It's called a dynamic array formula. Such formulas are new in Excel in Microsoft 365 and Office 2021 (and Excel Online).

See Dynamic array formulas in Excel

[hemrajrav328]

Can I edit the formula to accommodate time period ?

[HansV]

Sure! Let's say you enter the start date in B1 and the end date in B2. You can then use

=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))

See the attached version.

Table.xlsx

[hemrajrav328]

Can i add sumifs to the filter ? so instead of showing 1 name(column D), it list all names with the total idle for a selected date.

[HansV]

Can you provide an example of the expected output?

[hemrajrav328]

Please see atached

[HansV]

In the attached version, I have added two solutions: one using a pivot table, the other using formulas.

Table (1).xlsx

[hemrajrav328]

Thanks.

How do i eliminate a a specific criteria using the below formula.

Eg. Leave out staff 5

Please use the same table you used to write the below formula from earlier in this discussion.

=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))

[hemrajrav328]

Assuming the formula was modified to list name and idle intervals by date.

[HansV]

That formula already filters just one staff name: the one listed in R3.

[hemrajrav328]

Can subtotal be added at the bottom of a dynamic array formula ?

[HansV]

If you have Microsoft 365:

Table (1).xlsx