I am in need of a formula that will count the number of entries in one column like below.

Christopher Flemings

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?

Paul Newcome

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

Ray Lindstrom

Hi @Christopher Flemings,

Try this!

=COUNTM(Entries@row)

Hope it works!

BRgds,

-Ray

Paul Newcome

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

Ray Lindstrom

@Paul Newcome,

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.

Ray Lindstrom

@Paul Newcome,

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.

Christopher Flemings

@Ray Lindstrom @Paul Newcome

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

Ray Lindstrom

@Christopher Flemings,

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)

Paul Newcome

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

Ray Lindstrom

@Paul Newcome

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?

Christopher Flemings

@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

Paul Newcome

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