CountIFS w/Three Sheet References

cvarela

I am trying to get a CountIFS formula to work that includes three sheet references and I get errors no matter what I do. I am trying to count the number of tasks with the isParent checkbox not checked, the Status equaling a specific value, and the Category also equaling a specific value. The values are represented in the cell references in the formulas I have most recently tried. What am I doing wrong? I don't care which one works, I just need something that works. Thank you in advance for your time and assistance.

Error: #INCORRECT ARGUEMENT

Error: #INVALID OPERATION

Hunter Taylor

Hi Cvarela,

Here's a general approach that might help you set up your formula correctly:

1. Cross-Sheet References: Smartsheet requires you to create cross-sheet references individually and use them in the formula. First, make sure you’ve created separate cross-sheet references for each column you’re referencing (e.g., isParent, Status, Category columns on other sheets).
2. Using COUNTIFS in Smartsheet: Assuming you have named your references correctly, your formula should look like this: =COUNTIFS({isParent Reference}, 0, {Status Reference}, "Specific Status", {Category Reference}, "Specific Category")
   • Replace {isParent Reference}, {Status Reference}, and {Category Reference} with the actual names of your cross-sheet references.
   • Use 0 to check that the isParent box is not checked (assuming an unchecked checkbox is represented as 0 in your sheet).
   • Replace "Specific Status" and "Specific Category" with the actual values you’re filtering for.
3. Troubleshooting Tips:
   • Check Reference Names: Make sure each reference is created properly by selecting them from the dropdown in the formula editor.
   • Validate Each Condition: Sometimes it helps to test each condition independently with COUNTIF to ensure each individual reference works before combining them.
   • Formatting: Ensure there are no extra spaces around the references, values, or commas in the formula.

If this doesn’t work, let me know what specific error messages you're seeing, and I can help you troubleshoot further.

Hunter Taylor

Hi Cvarela,

Here's a general approach that might help you set up your formula correctly:

1. Cross-Sheet References: Smartsheet requires you to create cross-sheet references individually and use them in the formula. First, make sure you’ve created separate cross-sheet references for each column you’re referencing (e.g., isParent, Status, Category columns on other sheets).
2. Using COUNTIFS in Smartsheet: Assuming you have named your references correctly, your formula should look like this: =COUNTIFS({isParent Reference}, 0, {Status Reference}, "Specific Status", {Category Reference}, "Specific Category")
   • Replace {isParent Reference}, {Status Reference}, and {Category Reference} with the actual names of your cross-sheet references.
   • Use 0 to check that the isParent box is not checked (assuming an unchecked checkbox is represented as 0 in your sheet).
   • Replace "Specific Status" and "Specific Category" with the actual values you’re filtering for.
3. Troubleshooting Tips:
   • Check Reference Names: Make sure each reference is created properly by selecting them from the dropdown in the formula editor.
   • Validate Each Condition: Sometimes it helps to test each condition independently with COUNTIF to ensure each individual reference works before combining them.
   • Formatting: Ensure there are no extra spaces around the references, values, or commas in the formula.

If this doesn’t work, let me know what specific error messages you're seeing, and I can help you troubleshoot further.

cvarela

@Hunter Taylor Thank you for your response. My sheet references are accurate and the errors I'm receiving are above each screen shot in my first post. While the detail you provided is awesome, it addresses everything I've already checked.

Hunter Taylor

The next step to take then would be to test each condition separately in a simpler formula to confirm each reference and condition work. For example:

• =COUNTIF({isParent Reference}, 0)
• =COUNTIF({Status Reference}, "Specific Status")
• =COUNTIF({Category Reference}, "Specific Category")

If any of these do not return a value then there is a problem with the reference.

cvarela

I figured it out that it is the Status reference. The column that I am pulling from has a column formula that populates each cell. Do you know if that negates the capability of the CountIFS function?

cvarela

I figured it out with using a Count Collect formula instead. Thanks for your help.