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
-
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
-
@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.
-
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"
-
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)))
-
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. :)
-
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.
-
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.
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!