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.

Find more posts tagged with

Accepted answers

DKazatsky2

@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)))

All comments

Mark.poole

@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.

DKazatsky2

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

Craig Lemberger

@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"

DKazatsky2

@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)))

Mark.poole

@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.

Craig Lemberger

@Mark.poole this gives an incorrect argument message.

Craig Lemberger

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

Marcela

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

Craig Lemberger

@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)?

Craig Lemberger

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

Craig Lemberger

@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.

DKazatsky2

@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.

Craig Lemberger

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

DKazatsky2

@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)))

Craig Lemberger

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".

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.