CountIf formula is giving inconsistent results

I'm trying to replicate the =CountIf formula that works well in one sheet that resides in workspace A. I copied the report from A to workspace B. The formulas didn't copy over the same, lost the reference. Both reports pull from different project plan sheets but the Assigned column is the same in both plans. I am open to suggestions. Please see Attached.


Tags:

Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/20/23 Answer ✓

    Hi @Kathy PPT! In the below formula, taken from your first issue, you aren't using the CONTAINS function correctly, which is why that particular formula is UNPARSABLE (see the link near the bottom for how to use the CONTAINS function). However, if you are just looking for instances of "Ben xxxx" in a list of names on another page, then the CONTAINS function is overkill -- see the next issue to do this correctly.

    In the below screenshot, you took a formula from another sheet and pasted the entire thing into this sheet, without relinking the {Assigned} reference. The reason I know that is because you don't have an "Edit reference" link under that formula (like you do in the screenshot above) and the issue is #INVALID REF.

    To fix this, right click any cell on the page and select Manage References from the dropdown. Click the Create button and navigate to the sheet you want to reference. Select the column on that sheet you need, THEN change the "Sheet reference name" to "Assigned", just as I've done in the below screenshot. Select Insert Reference at the bottom of the screen. All your instances of {Assigned} on the page will now start to work.


    Here's a link to how to use the CONTAINS function:

    CONTAINS Function | Smartsheet Learning Center

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Kathy PPT, you can't copy cross-sheet references from one sheet to another. Cross sheet references are the references surrounded with the squiggle brackets, like {This}. Just delete the cross-sheet references and recreate them in the new sheet.

  • Kathy PPT
    Kathy PPT ✭✭✭✭

    Hi @Lucas Rayala, I appreciate your reply. I tried recreating the cross-sheet references in the new sheet but those attempts didn't work. Please see the original attachments: ...issue and issue2. Any other suggestions, please?

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/20/23 Answer ✓

    Hi @Kathy PPT! In the below formula, taken from your first issue, you aren't using the CONTAINS function correctly, which is why that particular formula is UNPARSABLE (see the link near the bottom for how to use the CONTAINS function). However, if you are just looking for instances of "Ben xxxx" in a list of names on another page, then the CONTAINS function is overkill -- see the next issue to do this correctly.

    In the below screenshot, you took a formula from another sheet and pasted the entire thing into this sheet, without relinking the {Assigned} reference. The reason I know that is because you don't have an "Edit reference" link under that formula (like you do in the screenshot above) and the issue is #INVALID REF.

    To fix this, right click any cell on the page and select Manage References from the dropdown. Click the Create button and navigate to the sheet you want to reference. Select the column on that sheet you need, THEN change the "Sheet reference name" to "Assigned", just as I've done in the below screenshot. Select Insert Reference at the bottom of the screen. All your instances of {Assigned} on the page will now start to work.


    Here's a link to how to use the CONTAINS function:

    CONTAINS Function | Smartsheet Learning Center

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!