How to use MATCH to get results within varying timestamp periods

Try this in B2 of sheet A:

=SUMIFS('DATA 1'!E:E,'DATA 1'!$A:$A,">="&A2,'DATA 1'!A:A,"<"&A2+1/24)

and this in C2:

=IFERROR(INDEX('DATA 2'!E:E,MATCH(INT(A2),'DATA 2'!A:A,0)),"")

Copy down as required.

Hope this helps.

Pete

EDIT: To suppress the zeros that you get in column B, apply this Custom Format to B2 before copying it down:

General;-General;

(the semicolons are important)

Thanks Pete. Looks like its working. I should have included additional information on my datasets, but didn't because I was assuming a "one formula fits all" solution.

The additional information: There will be a Data 3 dataset with weekly periods, and a Data 4 with monthly periods. Would either of the formulas in your suggestion work for those longer periods? Or would I need new formulas? (the additional info would be in columns D and E of sheet A.)

I appreciate your help on this.

Attach another workbook with the extra data in there. I'll take a look at it tomorrow, as it's getting a bit late here.

Pete

Revised workbook posted. Thanks!

I'm away for a few days and don't have Excel on this tablet, so I won't be able to look at this till the weekend.

Pete

Hi Pete I figured it out. For the 1 week to 1 month timeframes I used this structure (just adding x number of days to the "<" operator):

Please Login or Register  to view this content.

Look OK to you?

Yes, that looks fine. Glad you got it to work on your own.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete

Thanks again Pete.