Calculate absences within a period

[marley123]

Hello

I'm hoping you can help if possible please.

I have a spreadsheet with 3 tabs, on tab 1 is the formulas / calculations, tab 2 holds all the data and tab 3 has dates (start/end date).

I am trying to calculate how many occasions of absence an employee has within a 6 month period and return that value (as a number).

Employee ID unique - in column A on tab 1 and column A on tab 2
Start date of date range in tab 3 H1
End date of date range in tab 3 H2
Occasions of absence are 1 per row in tab 2, with employee ID in column A, start date of absence in column H and end date of absence in column I.

I've tried the following but it is returning #N/A. All the examples I see online use the formula on the same tab as the data and date - is there any issue with me referring to cells/data in different tabs of the same worksheet?

Formula in cell on Tab1:

=SUMPRODUCT(--(Tab2!H2:H630>=Tab3!H1),--(Tab2!I2:I630<=Tab3!H2),--(Tab2!A2:A630=A2))

I imagine this is probably a fairly easily formula, I'm just struggling to get it to work, any help much appreciated!

Kind Regards
Marie

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Can you post the data along with some manually typed expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[marley123]

 

[marley123]

 

[marley123]

So I've moved the data to a new sheet to anonymise etc and the formula works. However, when I return to my excel sheet it doesn't... I'm not sure why this would be?

 

[marley123]

H:H is the start date of the period and I:I is the end date of the period. Column A is the employee ID (number)

 

[marley123]

can anyone help?

 

[Fluff]

Make sure that you do not have any #N/A errors in cola H, I & A