Solved: Formula to total # of cells based on criteria

I am trying to figure out a formula that will give me the total count of cells to match my criteria below:

Criteria:
Is the first Non-Rotational Location in the same state as the University?
If so, how many are there?

The state (spelled out completely) of each location is in the "Location" Tab
The location list will be a dynamic list.

I would like a formula that can find the state for each 1st Non-Rotational Location shown above and then see if it is the same as the "State of University" of the cell next to it.
If it is a match, it should count it and provide a sum of the total matches.

First, change your dynamic range "Location" to be 2 columns wide (change the last "1" to a 2).

In G2 dragged down
=IF(VLOOKUP(E2,Location,2,FALSE)=Table!A2,SUMPRODUCT(--($E$2:$E$21=E2),--($A$2:$A$21=A2)),"NO")
Does that work for you?

Question: When you have duplicates (i.e. you have two "Utah- Sandy Ut entries) do they count as 1 or 2?

I couldn't come up with a way without dummy columns. In G2 dragged down

=VLOOKUP(E2,Location,2,0)=A2 (You can hide that column if you like)

In H2
=COUNTIF(G2:G21,TRUE)
Does that work for you?

As per Daddy LongLegs;

=SUM(IF(ISNA(LOOKUP(E2:E21,Location)),"",IF((LOOKUP(E2:E21,Location)=A2:A21)*(COUNTIF(INDEX(Location ,0,1),E2:E21)),1)))
entered as an array (CNTRL + SHFT + ENTER).

....thanks ChemistB, I also realised that could be simplified a little, i.e.

=SUM(IF(COUNTIF(INDEX(Location,0,1),E2:E21),IF(LOOKUP(E2:E21,Location)=A2:A21,1)))

confirmed with CTRL+SHIFT+ENTER

The lookup range (1st column of location) needs to be sorted ascending

Originally Posted by ChemistB

As per Daddy LongLegs;

=SUM(IF(ISNA(LOOKUP(E2:E21,Location)),"",IF((LOOKUP(E2:E21,Location)=A2:A21)*(COUNTIF(INDEX(Location ,0,1),E2:E21)),1)))
entered as an array (CNTRL + SHFT + ENTER).

Originally Posted by daddylonglegs

....thanks ChemistB, I also realised that could be simplified a little, i.e.

=SUM(IF(COUNTIF(INDEX(Location,0,1),E2:E21),IF(LOOKUP(E2:E21,Location)=A2:A21,1)))

confirmed with CTRL+SHIFT+ENTER

The lookup range (1st column of location) needs to be sorted ascending

Thanks for the help. Props go out to you.