I need a formula that will give me a number based on two different cells in a sheet.

Christopher Flemings

PSA Opt 8N

HFSN

HFSN

HFSN

HFSN

HFSN

HFSN

H FAC SV 1 Opt 3

H FAC SV 1 Opt 3

H FAC SV 2 Opt 3

H FAC SV 2 Opt 3

H FAC SV 2 Opt 3

H FAC SV 3 Opt 1

H FAC SV 3 Opt 3

Here is a small section of the sheet I need this formula for. The next cell has the name of the person working in that title, however, some positions in the sheet are vacant and have "Vacant" in the cell instead of a name.

I made a column for each of the titles that I need to focus on. Column one for instance is HFSN, while Column two is H FAC SV 1, 2, 3 & all Options. Some titles have unique names but contain specific references to the text needed for instance in the title column it could read HFSN Northern Section.

Originally I just took this formula and placed it into any cell that contained what I need a number for.

=IF([Current Incumbent]@row = "Vacant", 1, "") which gave me an accurate count of vacant positions based on titles.

This is an accurate count of vacant positions using the formula above but I would like to use a function formula that would count the same thing without me having to place a single formula into a cell on a sheet like the one above that has over 200 rows in it.

Anyone have any ideas?

Jeff Reisman

@Christopher Flemings

Can you clarify this?

"I would like to use a function formula that would count the same thing without me having to place a single formula into a cell on a sheet like the one above that has over 200 rows in it."

You gave a lot of information but I don't have a clear picture of what you're asking for. Can you kind of summarize it with some more general terms, such as Column A, Column B, etc?

Christopher Flemings

Thanks Jeff,

So the sheet is an organizational chart with around 800 plus rows, that shows all the different positions in our section, there are different titles for each position. These titles are listed in a title column. Then there is a column next to that that lists the individual who is working under that title labeled Current Incumbent, if no one is working in that position then there is the words Vacant there in that cell. In the snap shot I included is the columns of the titles that I want to show on a dashboard along with a number of vacant positions under that. By using the formula =IF([Current Incumbent]@row = "Vacant", 1, "") which works if I place it in the cell next to the title I want, but if I turn this formula in a column formula it counts every single Vacant in the sheet under that column which is inaccurate because I need only the titles listed at the top of that column. Here is the caveat some titles in the sheet have more than what I am looking for.

Column A = Titles

Column B = Current Incumbent

Column C = the number of Vacant in Column B(current incumbents), that match the number of times the title shows up in column A(Titles), However, I need the formula to look for specific text like SPSA, or HFSN, or, H FAC SV.