Counting Days and Nights in Attendance

[smbyrnecarey]

Hi

I would like to insert a table in my sheet which counts the types of leave taken against the days of the week. I've included the table but I need a bit of help with the formula to insert into Cell B2. All help gratefully accepted.

[AliGW]

Are you still using Excel 2016?

[AliGW]

In B2:

=SUMPRODUCT((Table25[[Matthew]:[John]]=[@[Leave type]])*($K$8:$K$463=Table5[[#Headers],[Monday]]))

Change the bit in red when you copy it to C2, D2, etc.

[smbyrnecarey]

Thank you so much for your very speedy and very accurate solution. I will do my best to learn from it.

I've realised that I wanted to capture slightly different data and if you have the time and patience, I would be so grateful for your advice. What I want to is create a table that identifies how often staff e.g. Matthew takes days and nights off and what day of the week they are. I want to establish whether both shift types are being taken proportionally across all days of the week.

If you can help that would be great but also fine if I have run out of favours or broken a rule by including a new question. Same attachment applies

[AliGW]

This is a completely different question.

Start a new thread with a suitable title and a fully-annotated workbook attached.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[smbyrnecarey]

Hi AliGW

I am so sorry I omitted to update the table. As attached

[AliGW]

Please start a NEW thread, as advised above.

This one is solved, and needs marking as such. Thanks.