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

[VSM]

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*")

Thank you!

 

[Bob Phillips]

Try

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

or

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

 

[VSM]

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]

I tested with some simulated data and it worked, so can you post the workbook so we can test better?

 

[VSM]

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!

 

[Bob Phillips]

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]

It can also be done with COUNTIFS

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

A bit neater.

 

[VSM]

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!

 

[Bob Phillips]

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]

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]

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

 

[VSM]

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!

 

[VSM]

Hi Bob,

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

 

[Bob Phillips]

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]

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

 

[VSM]

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!