Results 1 to 10 of 16

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

    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

Posting Permissions

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