tera dactil
New Member
- Joined
- May 18, 2020
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hello everyone. I have a table with two columns that I need to query: Effective Date, Effective Time.
I also have a lookup table which delineates a 24 hour interval:
etc
So, my objective is to count how many events in the Table fall within the hourly intervals. So that i can build a matrix in a summary worksheet that gives volumes by hour:
00:00-00:59 9
01:00-01:59 2
02:00-02:59 7
etc
I can get COUNTIFS to work by doing this:
=COUNTIFS('Table1'!A2:A100,">="&'Table2'!A1,'Table1'!A2:A100,"<="&'Table2'!A2
(this would be for the first result row. Then i replicate it with different lookups for the successive hourly intervals.
Obviously, the limitation here is that the array lookup in Table1 is static; I'd ideally like to have a match on the date on my summary worksheet against any qualifying dates in Table1. I've spent a day trying to use a combination of COUNTIFS, INDEX, MATCH, SUMPRODUCT, and it has stumped me. Any help would be much appreciated!
| A | B |
| 02/02/2020 | 07:01 |
| 05/03/2020 | 17:59 |
| 02/01/2020 | 08:00 |
| 02/02/2020 | 13:01 |
I also have a lookup table which delineates a 24 hour interval:
| A | B |
| Hour 0 start | 00:01 |
| Hour 0 end | 00:59 |
| Hour 1 start | 01:00 |
| Hour 1 end | 01:59 |
etc
So, my objective is to count how many events in the Table fall within the hourly intervals. So that i can build a matrix in a summary worksheet that gives volumes by hour:
00:00-00:59 9
01:00-01:59 2
02:00-02:59 7
etc
I can get COUNTIFS to work by doing this:
=COUNTIFS('Table1'!A2:A100,">="&'Table2'!A1,'Table1'!A2:A100,"<="&'Table2'!A2
(this would be for the first result row. Then i replicate it with different lookups for the successive hourly intervals.
Obviously, the limitation here is that the array lookup in Table1 is static; I'd ideally like to have a match on the date on my summary worksheet against any qualifying dates in Table1. I've spent a day trying to use a combination of COUNTIFS, INDEX, MATCH, SUMPRODUCT, and it has stumped me. Any help would be much appreciated!
