please help COUNTIF using time as criteria - but remove hidden cells from count

[adrian.s]

Hi all,

have a number of questions with a table I'm trying to make easier to use on a daily basis.

just a bit of a background, the main table on sheet2 is a direct export from a report generated in SAP, I have filtered the rows to only encompass 01/10/2020 and sorted column H (time) from earliest to latest.

I'm trying to create a graph that is on sheet1 daily and rather then going through manually completing the table in AD:532 to AN:534 I'm trying to get the information to draw from the main table automatically.

1. the first problem I have is getting lines picked (the number of records that fall between a given time I:E 6am -7am in column AD) the formula I used in cell AD:532 works but it also includes hidden cells from other days so it returns 17 but what I really want is just for the date 01/10/2020 so it should be returning 2.

2. the second problem is my lack of knowledge, the next step is getting a sum for total kg picked in each hour from column O:O and sum of picked quantity from Column Q:Q.

I have manually filled the secondary table (AD:532 to AN:534) to show what results I'm trying to get.

appreciate any assistance I can get.

regards,

Adrian

[bebo021999]

With AD1 is date criterie, i.e 1-Oct-2020,
AD532

Please Login or Register  to view this content.

AD533:

Please Login or Register  to view this content.

AD534

Please Login or Register  to view this content.

Drag all accross

[wk9128]

AD532 cell , Array formula , Drag to right

AD533 cell , Array formula , Drag to right

AD534 cell , Array formula , Drag to right

[adrian.s]

thank-you bebo021999,

works perfectly, appreciate the help.

regards,
adrian

[wk9128]

Thank You for the feedback and for marking the thread as 'Solved'.