Thread: Calculating Date and Numeric values

I have a worksheet with the first column showing dates in blocks of 5 at a time (working week)
There is a space of two rows between each block.
The date column runs for a complete year.

There is another column which shows a total of hours worked per week (sum of 5 working week columns)

I wish to create two date cells and one numeric cell, which is based on ---

IF first date cell is equal to ANY date in my first column, AND IF the second date cell is equal to ANY date in the same column, my numeric cell will show the SUM of all hours in my total hours worked column for that period.

I have tried - =IF(F4=A10:A265,and J4=A10:A265,J10:J265) but it doesnt work

If I apply this formula to a block of dates (5 only) with no blank rows, then it appears to work.

Am I on the right track, or can someone advise me other please.

Thank you

Ian

awaiting reply to moderator's request

@Richard: apologies, working on formula and didn't refresh the thread.

Regards

Thanks for changing the thread title as requested.

One way:

=IF(AND(COUNTIF($A:$A,F4)>0,COUNTIF($A:$A,J4)>0),SUM(INDEX($B:$B,MATCH(F4,$A:$A,0)):INDEX($B:$B,MATC H(J4,$A:$A,0))),"One or more dates not present")


Regards

Thanks for the rep.

If this has answered your question, please mark your thread as solved.

Regards