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

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
O365
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*")
Capture.PNG
wHA1CfRPOhFQAAAAABJRU5ErkJggg==


Thank you!
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

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

or

=COUNTIFS(H4:H21,"*IC*",H4:H21,"<>*IC*STAT*")
 
Last edited:

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I tested with some simulated data and it worked, so can you post the workbook so we can test better?
 

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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!
 

Attachments

  • Test book.xlsx
    13.6 KB · Views: 1

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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"))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
It can also be done with COUNTIFS

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

A bit neater.
 

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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!
 

Attachments

  • Test book2.xlsx
    16.2 KB · Views: 2

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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")
 

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Can you post a workbook with sample data and expected results?
 

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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!
 

Attachments

  • Test book3.xlsx
    20.9 KB · Views: 1
Last edited:

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
O365
Hi Bob,

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

Attachments

  • Test book4.xlsx
    22.5 KB · Views: 2

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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)))))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
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 ADept A
ICSTAT
WHWI
--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 ADept B
ICIC
WHFLX
--PO

Absences
Dept ADept B
STATVAC
WIWI
VACLEAVE


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:

VSM

New member
Joined
Feb 13, 2018
Messages
19
Reaction score
0
Points
0
Excel Version(s)
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!
 
Top