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

Options

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.

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/07/24 Answer ✓
    Options

    @aneekahTAL

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

    Create a cross sheet reference 

    1. 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. 
    2. In the help card, select Reference Another Sheet.
    3. Search for the sheet where the data exists.
    4. In the search results, select the source sheet.
    5. 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.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/07/24
    Options

    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.

  • aneekahTAL
    aneekahTAL ✭✭✭
    Options

    What do you mean by cross sheet reference?

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/07/24 Answer ✓
    Options

    @aneekahTAL

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

    Create a cross sheet reference 

    1. 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. 
    2. In the help card, select Reference Another Sheet.
    3. Search for the sheet where the data exists.
    4. In the search results, select the source sheet.
    5. 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.

  • aneekahTAL
    aneekahTAL ✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!