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!

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.