Count based on multiple criteria

good day

my workbook consists of sheets depicting each day of the month. (1st to 31st)
i want to count the amount of people that went absent in a month. the staff are working shifts. if i just do a countif(s) then i am counting the amount of times annual leave occurs. this is obviously wrong as one person can be marked as annual leave for a number of days. if Paul and Peter was on shift A, then if Paul was on annual leave from 1st of the month till the 10th that will count as 1 occurrence. if Peter was on annual leave from the 7th till the 20th that counts as 1 occurrence but my total count on the Summary sheet for shift A will show 2.
If Jamie and john was on shift B with the same dates then the summary sheet will show that for shift B.
here is the breakdown of the workbook:
1. Named Range = SheetList to contain all sheets in workbook
2. F5 = cell showing specific shift on duty (A, B, C or D)
3. Range D919 = cells containing staff names
4. Range E9:E19 = cells containing absenteeism reason ie. Annual Leave, Sick, etc
5. Summary Sheet B5 = where i wish to show how many staff was absent for shift A
6. Summary Sheet B6 = where i wish to show how many staff was absent for shift B

i tried an array formula but this doesnt make provision for the same person marked as absent for a number of days.... therefore the totals are skewed.
{=SUM(IF(COUNTIFS(INDIRECT("'"&SheetList&"'!F5");B$11);COUNTIFS(INDIRECT("'"&SheetList&"'!E9:E37");$B12)))}

your assistance is appreciated...

roninn75 said:

your assistance is appreciated...

Click to expand...

If it suits you extra column see my idea

Formula in to A5 on Summary Sheet

navic said:

If it suits you extra column see my idea

Formula in to A5 on Summary Sheet

Click to expand...

thank you for your response, but if you just look at shift A in the sample workbook, Shaun was on annual leave for consecutive days... therefore it should be counted as 1 and not 4 as in your sample.

regards

roninn75 said:

Shaun was on annual leave for consecutive days... therefore it should be counted as 1

Click to expand...

Okay, probably I did not understand you well
Here's a new idea. There are a couple of additional worksheets that you can hide
I hope that this example now, can you help solve the problem, if nothing else at least give you an idea.
So much from me, my respect

thank you Navic... I can work with this... just some clarity please... can you explain the indirect function in this formula:
=IF(INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9))=0,"",INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9)))

roninn75 said:

can you explain the indirect function in this formula:
=IF(INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9))=0,"",INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9)))

Click to expand...

If you look Evaluate Formula, you can see that this part of the formula sets the name of the worksheet and address of cell.
So the result of this part of the formula is '1'!$D9, it relates to cell D9 on the worksheet named "1"

This formula takes the data from cell A1 and that is the number 1, which is identical to the name of the worksheet to which pulls data.
This part of formula "'"&A$1 is reference to a cell A1 and data inside on the active sheet. In cell A1 we have number 1, which is the name of the first worksheet

This part of formula ROW(D9), return number 9, So "D"&ROW(D9) => D9

Indirect function returns data from the address cell
This formula =INDIRECT("'1'!$D9") returns the data from worksheet named 1 from D9 cell, that is the result of Paul or other data and it depends on the name of the worksheet

Respective formula could write without the IF function, but I was nestled so that I avoid the appearance of results 0

I hope that I was able to explain the respective formula

my regards