Formula needed to count occurrence of a field value across multiple sheets

Scenario:

Sheet A contains a field : Value Proposition. There are 8 rows in Sheet A.

VP1

VP2

VP3 etc

Sheet B and Sheet C have long lists of tasks, each task is assigned a Value Proposition (in field Value Proposition)

In Sheet D (which is a Metrics Sheet) I need a formula to count the number of times that a Value Proposition in Sheet A appears in Sheet B and Sheet C.

Expected Result:

Value Proposition | Total

VP1 | 6

VP2 | 3

VP3 | 7 etc

please help.

Find more posts tagged with

Formulas

Accepted answers

Mark.poole

@aneekahTAL

From https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Create a cross sheet reference

Build the formula in the cell where you want the data to appear.
For example, type =COUNT(
The formula help card appears above or below your cell.
In the help card, select Reference Another Sheet.
Search for the sheet where the data exists.
In the search results, select the source sheet.
Select the cell range containing the data you want to reference, and select Insert Reference.

All comments

Mark.poole

Create a cross sheet reference for each Column in in sheet b and c that has the information. In your Metrics sheet I am assuming you have a column that has VP1, VP2 and so on. In this case ill call it Value Proposition. Try this formula In your Total Column once your references are done.

=Countif({Ref B},[Value Proposistion]@row)+Countif{Ref C},[Value Proposistion]@row

aneekahTAL

What do you mean by cross sheet reference?

Mark.poole

@aneekahTAL

From https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Create a cross sheet reference

Build the formula in the cell where you want the data to appear.
For example, type =COUNT(
The formula help card appears above or below your cell.
In the help card, select Reference Another Sheet.
Search for the sheet where the data exists.
In the search results, select the source sheet.
Select the cell range containing the data you want to reference, and select Insert Reference.

aneekahTAL

Thanks, I managed to work it out and get the desired results.

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.