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


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!

Ryan Sides

Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

Come Say Hello!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!