Number of instances a person has been off sick

[Hazeyt]

I have a spreadsheet with all different kind of staff absence record types that are linked by a staff ID number. I want to calculate the number of instances that a member of staff has been off for sick leave only. I have added a column with an indicator to show the staff who have been off on sick leave. I have tried sumif using the ID number and Sick leave indictor and also count to try calculate the number of instances but it doesn't seem to work. Please can anyone help? Thanks

 

[James006]

Hi,
With the assumption your names are located in range B2:B10 and the Sickness indicator in A2:A10
=SUMPRODUCT((B2:B10="John")*(A2:A10="S"))

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.

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.

 

[Hazeyt]

Hi,
With the assumption your names are located in range B2:B10 and the Sickness indicator in A2:A10
=SUMPRODUCT((B2:B10="John")*(A2:A10="S"))

Thank you. I need to use the Staff ID number to identify how many times that person has been off. Would I add this on to the end of the formula?

 

[James006]

Thank you. I need to use the Staff ID number to identify how many times that person has been off. Would I add this on to the end of the formula?

In fact the element (B2:B10="John") is your way to identify your staff ... so you need to replace with (B2:B10="the Staff_ID_Number_Selected")

 

[Hazeyt]

In fact the element (B2:B10="John") is your way to identify your staff ... so you need to replace with (B2:B10="the Staff_ID_Number_Selected")

Thank you