Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

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

  1. #1
    Seeker VSM's Avatar
    Join Date
    Feb 2018
    Posts
    19
    Articles
    0
    Excel Version
    O365

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



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

    Hello,

    I am trying to count cells in a range that meet a text criteria. One addition to this is I want to subtract from the count if the cell below a matched cell is equal to another certain text criteria.
    I have been able to count the cells in a column that contain "*IC*" but I cannot figure out how to subtract if the cell below matches the other text criteria: "STAT". When I input a COUNTIF or COUNTIFS formula it returns a count of 4 when it should return a count of 2 (because I want to subtract if it says STAT below it). I have attempted to use an INDEX/MATCH function within the COUNTIFS formula But I have been unsuccessful.

    Formula I am using to count:

    =COUNTIF(H4:H21,"*IC*")
    Click image for larger version. 

Name:	Capture.PNG 
Views:	35 
Size:	4.7 KB 
ID:	10814


    Thank you!
    Last edited by Bob Phillips; 2022-01-28 at 12:35 AM. Reason: Remove HTML tags for clarity

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

    =COUNTIF(H4:H21,"*IC*")-COUNTIF(H4:H21,"*IC*STAT*")

    or

    =COUNTIFS(H4:H21,"*IC*",H4:H21,"<>*IC*STAT*")
    Last edited by Bob Phillips; 2022-01-28 at 12:39 AM.

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

    Thank you for your reply. I have tried the formulas you provided but unfortunately they still count the ones with STAT below it. I am hoping it can find all the cells with *IC* and count them but subtract if any of them of have STAT directly below. I was trying to get an INDEX/MATCH function in the COUNTIFS formula to look below the cell each time it finds IC but I could not get it to work.

    Thanks.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    I tested with some simulated data and it worked, so can you post the workbook so we can test better?

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

    I have taken a snippet out of my larger spreadsheet and attached it. You can see the two 4's below the table are where I inputted the formulas.

    Thanks!
    Attached Files Attached Files

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Aah, I thought the text was all in one cell, it is separate cells.

    Try this

    =SUMPRODUCT(--(ISNUMBER(FIND("IC",G4:G20))),--(G5:G21<>"STAT"))

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    It can also be done with COUNTIFS

    =COUNTIFS(G4:G20,"*IC*",G5:G21,"<>STAT")

    A bit neater.

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

    This latest version of the formula works great. I have one more issue though that I'm hoping a modification to the formula will solve.

    I need to be able to search a larger range instead of just the small one I provided in my sample (my mistake I thought it would work in a larger range).
    I have attached another sample spreadsheet to give you an idea. When I used the formula you provided the numbers weren't right if you change the range to encompass the whole column of data (G4:G111). In column G it returns a 5 for the whole column when it should be a 2. There are 5 total cells with *IC* but 3 of them have STAT below.

    Eventually I'd like to adapt this formula so I can group different lettered criteria's to get proper counts for certain positions and departments minus people that are off (STAT).

    Thanks you, I really appreciate your help and patience!
    Attached Files Attached Files

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    The mistake you have made is that the ranges have to be offset by 1 row. The check for *IC* has to be row 1...n-1, the check for STAT has to be for row 2...n. So the two formulae would be

    =SUMPRODUCT(--(ISNUMBER(FIND("IC",G4:G110))),--(G5:G111<>"STAT"))

    =COUNTIFS(G4:G110,"*IC*",G5:G111,"<>STAT")

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

    Thank you, that formula works great for the whole column. I have one last layer of complexity I am hoping you can help with!

    If I were to want to add additional criteria to the formula how would that be done?

    I would like to add (*WH*) and (WI). WH would be similar to IC in that I want to also count cells that contain it but if the cell below contains either STAT or WI, I want to subtract from the count.

    I know this isn't right but just for visual purposes:
    =COUNTIFS(G4:G110,"*IC*" OR "*WH*",G5:G111,"<>STAT" OR "<>WI")

    Thank you!

Page 1 of 2 1 2 LastLast

Posting Permissions

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