CountIf formula is giving inconsistent results

Kathy PPT

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.

CountIf Formula working.PNG

CountIf Formula issue2.PNG

CountIf Formula issue.PNG

Lucas Rayala

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

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

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

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