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.
Best Answer

From
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.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
 Build the formula in the cell where you want the data to appear.
Answers

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
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

What do you mean by cross sheet reference?

From
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.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
 Build the formula in the cell where you want the data to appear.

Thanks, I managed to work it out and get the desired results.
Help Article Resources
Categories
Check out the Formula Handbook template!