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

Options

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!

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!