Results 1 to 6 of 6

Thread: Count based on multiple criteria

  1. #1

    Count based on multiple criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by roninn75 View Post
    your assistance is appreciated...
    If it suits you extra column see my idea

    Formula in to A5 on Summary Sheet
    Code:
    =COUNTIF('1'!$G:$G;$A5&B$3)+COUNTIF('2'!$G:$G;$A5&B$3)+COUNTIF('3'!$G:$G;$A5&B$3)+COUNTIF('4'!$G:$G;$A5&B$3)+COUNTIF('5'!$G:$G;$A5&B$3)+COUNTIF('6'!$G:$G;$A5&B$3)+COUNTIF('7'!$G:$G;$A5&B$3)+COUNTIF('8'!$G:$G;$A5&B$3)+COUNTIF('9'!$G:$G;$A5&B$3)+COUNTIF('10'!$G:$G;$A5&B$3)+COUNTIF('11'!$G:$G;$A5&B$3)+COUNTIF('12'!$G:$G;$A5&B$3)+COUNTIF('13'!$G:$G;$A5&B$3)+COUNTIF('14'!$G:$G;$A5&B$3)+COUNTIF('15'!$G:$G;$A5&B$3)+COUNTIF('16'!$G:$G;$A5&B$3)+COUNTIF('17'!$G:$G;$A5&B$3)+COUNTIF('18'!$G:$G;$A5&B$3)+COUNTIF('19'!$G:$G;$A5&B$3)+COUNTIF('20'!$G:$G;$A5&B$3)+COUNTIF('21'!$G:$G;$A5&B$3)+COUNTIF('22'!$G:$G;$A5&B$3)+COUNTIF('23'!$G:$G;$A5&B$3)+COUNTIF('24'!$G:$G;$A5&B$3)+COUNTIF('25'!$G:$G;$A5&B$3)+COUNTIF('26'!$G:$G;$A5&B$3)+COUNTIF('27'!$G:$G;$A5&B$3)+COUNTIF('28'!$G:$G;$A5&B$3)+COUNTIF('29'!$G:$G;$A5&B$3)+COUNTIF('30'!$G:$G;$A5&B$3)+COUNTIF('31'!$G:$G;$A5&B$3)
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Quote Originally Posted by navic View Post
    If it suits you extra column see my idea

    Formula in to A5 on Summary Sheet
    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

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by roninn75 View Post
    Shaun was on annual leave for consecutive days... therefore it should be counted as 1
    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
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    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)))

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by roninn75 View Post
    can you explain the indirect function in this formula:
    =IF(INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9))=0,"",INDIRECT("'"&A$1&"'!$"&"D"&ROW(D9)))
    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
    Last edited by navic; 2015-07-20 at 08:38 PM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •