Question

I am working on a Dashboard, & for one widget, I would like to display a dynamically updating number of a specific position that has been hired.  I have one dropdown column that displays the different positions.  Another column has a dropdown list for Hiring Status: Hired, Pending, & Vacant.

I want to only display any value matching "Assistant Chief of Operations" in the first column, & "Hired" in the second.

I've looked at examples, & I can't seem to wrap my head around how to reference columns in another sheet while using formulas.  I think that the =COUNTIF formula is the correct one, but every iteration I try I get a result that unparseable.

I would appreciate any help or examples you can give me.  Thank you in advance.

Roketto · Accepted Answer

Hi @Kelly Moore, & thank you for your assistance.  Unfortunately, I did exactly what you said, & it still returned #INVALID REF.  Is there something wrong with how I typed it?

=COUNTIFS({Hiring Status Data Sheet Position Ref}, "Assistant Chief of Operations", {Hiring Status Data Sheet Hired/Pending/Vacant}, "Hired")

The {Hiring Status Data Sheet Position Ref} is a selection of the entire column containing the dropdown options for different positions.  The {Hiring Status Data Sheet Hired/Pending/Vacant} is a selection of the entire column containing the dropdown options for Hired, Pending, or Vacant.  Do you need any other information to help me troubleshoot this formula?

EDIT: On a hunch, I renamed my references to "Fire Station Position" & "Hiring Status."  Now it works!  I think perhaps the problem was the slashes in the second reference screwing with the code.  What a headbanger; it was such a simple solution!