Across 3 Sheets -> Count When Index Match (Cross Sheet from Sheet 2 to Sheet3) Equals Cell Value

Ryan Sides

I have 3 Sheets:

Sheet1: Metrics

Sheet2: List of Addresses (without States)

Sheet3: List of Zip Codes and their States

Without adding a column to Sheet2, what is the correct formula (to be placed on Sheet1) to count the number of rows on Sheet2 that have the same State shown on Sheet1?

Sheet1 Data

State | Count

LA | count formula needed here

FL | count formula needed here

Sheet2 Data

Street | City | Zip

123 Main | Miami | 33458

456 Third | Miami | 33404

Sheet3 Data

Zip | State

33458 | FL

33404 | FL

I've tried using COUNTIFS with INDEX MATCH and I can't get it to point at the second sheet (Cross sheet formulas) to perform the formula on each row in order to count it.

Any ideas? Thanks!

Paul Newcome

It is not going to be possible without adding another column to one or more of the sheets. Most ideally a single column to sheet 2.

Paul Newcome

It is not going to be possible without adding another column to one or more of the sheets. Most ideally a single column to sheet 2.

Ryan Sides

Thanks @Paul Newcome

I was racking my brain for a couple of hours over here! Whew.