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

[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!