Counting "Company Names" in a Numbers Column

Hi Nicholas,

Thank you for pasting your data (instead of a screen shot). That allowed me to see exactly what you see. Surprise! Trailing spaces after Delta Products and two trailing spaces in Row 7. So there are 8 "unique" companies.

Formula in B2 is COUNTIF(A$1:A2,A)

Formula in Footer B16 is COUNTIF(B,1)

The TRIM function is your friend.

From the Function Browser (the panel on the right):

The TRIM function returns a string based on a given string, after removing extra spaces.

TRIM(source-string)

source-string: Any value from which to remove extra spaces.

Notes

• TRIM removes all spaces before the first character, all spaces after the last character, and all duplicate spaces between characters, leaving only single spaces between words.

Formula in D2 is TRIM(A2)

Now the occurrences are correct (Column E).

Is that sufficient to answer your first 2 questions?

Your third question: the short answer is no. Numbers does not recognise text colour or any formatting in formulas. However, If you tell us your rules for setting the text colour, we may be able to help.

Regards,

Ian.

Have you tried a Pivot Table. It takes about 30 seconds to get this:

1. Click in the table with the company names (after you've made sure they don't have any invisible characters)
2. Choose Organize > Create Pivot Table > On Current Sheet
3. Drag the "fields" down into the boxes as shown

Done!

SG

NicholasPyers wrote:

The answer should be 7... not 14... based on the "sample data" I provided

And in the screenshot I posted the Pivot Table is on the right and clearly shows 7!

If you have too many to count easily the way you can in the example, just choose another option within the Pivot Table as Camelot has illustrated.

Or even easier, just select the visible cells in that column of the Pivot Table and look down at the bottom of the Numbers window where you will see COUNTA.

Pivot Tables are easy to use, and powerful.

More on Pivot Tables here (also at Help > Numbers Help in your menu):

Intro to pivot tables in Numbers on Mac - Apple Support

Another approach is to use an 'Is Distinct' filter.

With the powerful built-in functionality in recent versions of Numbers the old formula-based solutions for tasks like this are no longer necessary.

SG

Play a little with the Pivot Tables. They're super powerful for summarizing data.

In this case, SG's example showed the values being counted, which is the default, but not the only option.

Just click the (i) icon beside the 'Company (Count All)' field and you'll see other options:

Switching it to 'Count (Unique)' will give you a count of unique values.

Hmm... the plot thickens. Using a filter (cell is unique) makes no sense.

Sorry, but it has me beat.

Ian.