Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Count Formula

Good Morning,

I am attempting to count the number of "Box Name" appears on another sheet, if the Client Name on that sheet (named pruning data) is the same as the client name on the current sheet@row.

=IF(COUNTIF({Client Name(Pruning Data)}, [Client Name]@row) = 0, "", (COUNTIF({Box Name}, {Client Name(Pruning Data)} = [Client Name]@row),"")

It is unparsable.

If I change it to:

=IF(COUNTIF({Client Name(Pruning Data)}, [Client Name]@row) = 0, "", (COUNTIF({Box Name}, {Client Name(Pruning Data)} = [Client Name]@row)))

I just get a zero when I know there is data there to be counted. I've spent hours trying to get this work.

As always, help is so much appreciated.

Best Answer

  • Community Champion
    Answer ✓

    @Craig Lemberger,

    Is it that you won't every box name, even when repeated? Try with the DISTINCT removed.

    =IF(COUNTIF({Client Name(Pruning Data)}, @cell = [Client Name]@row) = 0, "", COUNT(COLLECT({Box Name}, {Client Name(Pruning Data)}, @cell = [Client Name]@row)))

Answers

  • Community Champion

    @Craig Lemberger

    It looks like you have a ( in a spot that's not needed. Remove the ( from in front of the second countif..

    =IF(COUNTIF({Client Name(Pruning Data)}, [Client Name]@row) = 0, "", COUNTIF({Box Name}, {Client Name(Pruning Data)} = [Client Name]@row))

    If you ever wonder if you have the correct number of () always check to make sure they are all color coded.

    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.

  • Community Champion

    Hi @Craig Lemberger,

    It looks like there is some syntax issues, but also, the formula doesn't appear to be setup correctly if I understand what you are trying to do.

    See if this works for you.

    =IF(COUNTIF({Client Name(Pruning Data)}, @cell = [Client Name]@row) = 0, "", COUNT(DISTINCT(COLLECT({Box Name}, {Client Name(Pruning Data)}, @cell = [Client Name]@row))))

    Hope this helps,

    Dave

  • ✭✭✭✭

    @Mark.poole Thank you. Even though I have three entries that should be counted, the formula still shows 0 as a result.

    and @DKazatsky2 the formula you so kindly presented comes up with 1.

    To summarize, the client name I'm testing should correspond to 3 counts of "Box Name"

  • Community Champion
    Answer ✓

    @Craig Lemberger,

    Is it that you won't every box name, even when repeated? Try with the DISTINCT removed.

    =IF(COUNTIF({Client Name(Pruning Data)}, @cell = [Client Name]@row) = 0, "", COUNT(COLLECT({Box Name}, {Client Name(Pruning Data)}, @cell = [Client Name]@row)))

  • Community Champion

    @Craig Lemberger

    I noticed an issue in how I presented a formula to you.

    First try this.

    =COUNTIFS({Box Name}, {Client Name(Pruning Data)},[Client Name]@row)

    See if that pulls 3. What this does is remove the conditional of the if statement first. It also changes your count IF to a COUNTIFS so you can have more then one criteria. I perfer countifs to countif even for single criteria when going across sheets.

    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.

  • ✭✭✭✭

    @Mark.poole this gives an incorrect argument message.

  • ✭✭✭✭

    @Mark.poole and I forgot to thank you again. :)

  • Employee

    Hello @Craig Lemberger

    Can you try this:

    =COUNTIFS({Box Name}, [Client Name]@row, {Client Name(Pruning Data)}, [Client Name]@row)

    This formula counts the number of times the client name in the current row appears in the specified box name and matches the client name in the pruning data.

    Hope this helps!

    Marce

    Need more information? 👀 | Help and Learning Center
    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • ✭✭✭✭

    @DKazatsky2 Yes, I want to count the number of times! your formula worked! Thank you!!

    I must say that I have not used the @cell like that. Is the @cell referring tothe "Client name (pruning)?

  • ✭✭✭✭

    @Marcela thank you. I tried that one as well and it comes up with 0.

  • ✭✭✭✭

    @DKazatsky2

    I want to thank you again for your help.

    =IF(COUNTIF({Client Name (Pruning)}, @cell = [Client Name]@row) = 0, "", COUNT(COLLECT({Box Hedge Name}, {Client Name (Pruning)}, @cell = [Client Name]@row)))

    This formula is what you gave me and it's great. Like I said above, I have never used the @cell this way, but it works!

    I am hoping you can help me with one thing: if for example, if the client Name is there but does not meet the criteria of Box Hedge, "0" results. I'd rather it remain blank. The formula is a bit complicated for me; I've tried multiple ways to insert "" to command a result of blank, but I can't get it to work.

    Much appreciated for your help.

  • Community Champion

    @Craig Lemberger,

    You don't actually need the @cell for this to work, I didn't mean to confuse things. The following should give the same results.

    =IF(COUNTIF({Client Name(Pruning Data)}, [Client Name]@row) = 0, "", COUNT(COLLECT({Box Name}, {Client Name(Pruning Data)}, [Client Name]@row)))

    As far as your last question, I'm not really following, the formula listed, as written, should not be counting any instances where Box Name is blank. Would you be able to provide a screen shot with some data and expected results? Being sure to not include sensitive data.

  • ✭✭✭✭

    I appreciate your clarification. Unfortunately for me I can't do a screen shot as the key board is not one that has a print screen button clearly labelled.

    What I can say is that the "Box Name" is one of several columns; others are called Yew Name; Spiraea Name and others. These are names of categories of shrubs which indicate that that shrub has been pruned.

    So if one of my employees prunes a Bowood (Box) for a client and fills out a form, that row will be copied to another sheet which is then read by a third sheet using the formula you helped me with. What is happening is that the other shrubs which are not pruned, get a 0 because the first part of the formula is true: a client with a particular name is present and her bowoods were pruned but the spiraea were not, yet the spiraea gets a "0".

    Is this clear?

    @DKazatsky2

  • Community Champion

    @Craig Lemberger

    This is the first time you have mentioned multiple columns. You can duplicate the formula for each column and ensure you create a new cross-sheet reference for the appropriate data - here is an example for spiraea.

    =IF(COUNTIF({Client Name(Pruning Data)}, [Client Name]@row) = 0, "", COUNT(COLLECT({Spiraea Name}, {Client Name(Pruning Data)}, [Client Name]@row)))

  • ✭✭✭✭

    Yes. I have been successful in duplicating for all of the plant material I am tracking.

    The "issue" is the following: If Client B has boxwood, spiraea but does not have roses, client be will get numbers to indicate the count for boxwood and spiraea and will also get a "0" for Roses. My team and I would rather see a blank space instead of a "0".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions