I am in need of a formula that will count the number of entries in one column like below.
I would like a column formula to auto populate column two "Total number of entries in left column" does anyone have a suggestion on how I can do this?
Best Answer
-
@Ray Lindstrom Zero vs blank is definitely personal preference, and I could see valid arguments for both. I'd make it zero like so:
=IF(Entries@row <> "", LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", "")), 0)
@Christopher Flemings Happy to help. 👍️
Answers
-
-
@Ray Lindstrom I don't know that COUNTM would work on this one because it is not a multi-select column (or at least it doesn't look like it).
It also looks like we have both line breaks and spaces being used as delimiters, so we may end up having to go with something more like this:
=LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", ""))
Basically we take the total character count (plus a delimiter after the last selection) then we subtract from it the character count after we remove the delimiter(s).
Let's just say we have 8 entries that are each 2 characters long. Add the delimiter after each selection and that gives us a total of 24 characters.
Since we have the delimiter at the end of every entry, when we remove those 8 we get 16.
24 - 16 = 8
We have 8 entries.
-
Excellent point about the COUNTM. Thanks for pointing that out.
Question about your solution: Will that work when entries are not all the same length?
This user's entries are all the same length in the screenshot with the exception of 1 that has "IL" as a prefix to the 6 digit number.
-
Can you change the column properties to Dropdown, Multi-Select, but not restrict to dropdown values?
Then you can use the COUNTM function, but can still manually enter values as needed.
hmmm. Returning to add another thought. This might work for "moving forward", but maybe not for the existing data due to the spaces used to separate values.
-
Paul's formula works regardless of the number within each character, however, this formula also counts blank cells as 1 here is a screenshot of that. Any way to eliminate the blank cells so I can get an accurate count from this sheet.
This is so close thank you both..
-
I'm glad it's giving you what you expect! I used that formula in a test sheet, and it results in a value of 1 more than how many entries I have listed.
Your next step is to wrap it in a "if not blank" statement which looks like this.
IF(NOT(ISBLANK(Column@row))
Which turns into this if you want nothing to appear in the cell if there are no entries:
=IF(NOT(ISBLANK(Entries@row)), (LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", ""))), "")
You can replace the else clause of "" with a 0 if you would like to show 0 entries. Then it would look like this:
=IF(NOT(ISBLANK(Entries@row)), (LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", ""))), 0)
-
@Ray Lindstrom Yes. It should work regardless of length of entries because we are looking at delimiters.
@Christopher Flemings I would use this to exclude blanks:
=IF(Entries@row <> "", LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", "")))
-
Thanks, now I understand how it doesn’t matter about blanks! :)
For the else in your IF statement, wouldn’t you want a 0 instead of “”, so it shows 0 entries if blank?
-
@Ray Lindstrom Actually I would prefer it to be blank, it makes it easier for other formulas to pick up the blank field later when they are in need of other things. Plus there is no room for error so blank is better in this case. The formula works fantastically the way it is..
Thanks
Chris
-
@Ray Lindstrom Zero vs blank is definitely personal preference, and I could see valid arguments for both. I'd make it zero like so:
=IF(Entries@row <> "", LEN(Entries@row + CHAR(10)) - LEN(SUBSTITUTE(SUBSTITUTE(Entries@row, CHAR(10), ""), " ", "")), 0)
@Christopher Flemings Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!