Page 2 of 2 FirstFirst 1 2
Results 11 to 16 of 16

Thread: COUNTIFS and INDEX/MATCH - How to count cells based on cell below it

  1. #11
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365


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

    Can you post a workbook with sample data and expected results?

  2. #12
    Seeker VSM's Avatar
    Join Date
    Feb 2018
    Posts
    19
    Articles
    0
    Excel Version
    O365
    Hi Bob,

    I have attached an updated spreadsheet example.

    In column G the expected result should be 3. There are a total of 7 cells that contain either *IC* or *WH*. However 4 of them have either STAT or WI below them.

    The end goal for me is I need the ability to count different position names like IC or WH and also be able to subtract if the person is off on STAT, WI or VAC.
    As you can see in the example there are many other names of positions other than IC and WH (MIX, FL PAST, etc.) and the same person isn't always working in the same position week after week, but I will be grouping them by departments using specific formulas for each department. There could be up to 5 or 6 criteria names I need to count by in a department and 3 name criteria I need to subtract if STAT, WI, or VAC are below one of those cells.

    I'm hoping to have a flexible formula that I can add or remove any number of name criteria I will need for each department. This will allow an accurate count of how many people are scheduled in each department minus who is off on STAT, WI or VAC.

    Thanks!
    Attached Files Attached Files
    Last edited by VSM; 2022-01-31 at 09:29 PM.

  3. #13
    Seeker VSM's Avatar
    Join Date
    Feb 2018
    Posts
    19
    Articles
    0
    Excel Version
    O365
    Hi Bob,

    Use this spreadsheet. The first one I uploaded was not the right one.
    Attached Files Attached Files

  4. #14
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Here is a generic formula that you can add, it ain't pretty though.

    =SUMPRODUCT(--(ISNUMBER(FIND({"IC","WH"},G4:G110))),
    --(NOT(ISNUMBER(FIND({"STAT","WI"},G5:G111)))))

  5. #15
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    If you want greater flexibility, extra posittions, different values for different departments, I would set up a couple of structured tables.

    To start, let's work with the current situation.

    Create a table, call it Positions, like so

    Dept A
    IC
    WH

    Create another table called Absences, like so

    Dept A
    STAT
    WI

    If you wanted to add a new absence type, just add it to the Absences table, but be sure to add another row to the Positions table, they must have the same number of rows. If one column has blank cells, I would put some value in there that will never be encountered on the real data, to ensure no hidden counting.

    The pair of tables would then look like so

    Positions.......Absences
    Dept A Dept A
    IC STAT
    WH WI
    -- VAC

    The formula would then be

    =SUM((ISNUMBER(FIND(TRANSPOSE(Positions[Dept A]),G4:G110)))*(NOT(ISNUMBER(FIND(TRANSPOSE(Absences[Dept A]),G5:G111)))))

    This is an array formula, so it must be array-entered.

    To cater for a different set of values for another department, add another column to Positions and Absence, for example,

    Positions
    Dept A Dept B
    IC IC
    WH FLX
    -- PO

    Absences
    Dept A Dept B
    STAT VAC
    WI WI
    VAC LEAVE


    and you would use Positions[Dept B] and Absences[Dept B] in the other formula (remembering always to offset the rows by one).
    Last edited by Bob Phillips; 2022-02-01 at 06:26 PM.

  6. #16
    Seeker VSM's Avatar
    Join Date
    Feb 2018
    Posts
    19
    Articles
    0
    Excel Version
    O365
    Hi Bob,

    Thank you very much for all of your help. You have saved me many hours and days of trying to figure this all out!

    Take care!

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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