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

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

2. 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!

3. Hi Bob,

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

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

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

6. 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!

Page 2 of 2 First 1 2

Posting Permissions

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