Need A Formula To Return A Number Based On Criteria From Two Columns In A Reference Sheet

Roketto

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

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!

Kelly Moore

Hey @Roketto

The COUNTIFS (plural) is the function used to count any number of criteria needed to filter the data. COUNTIFS can be used for for one criteria to how ever many is needed.

=COUNTIFS({source sheet Position column}, "Assistant Chief of Operations", {source sheet Hiring Status column}, "Hired")

Because you want to reference another sheet, you cannot simply copy paste this formula in but you must manually create the references through the formula window. If you need help with this, doesn't hesitate to ask me. You can also find an example here

Does this work for you?

Kelly

Roketto

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!