The Problem of COUNTIF in Apple Numbers

You have a couple options:

1) Post feedback to Apple using the menu item "Numbers > Provide Numbers Feedback" and wait for Apple or

2) Try a way that works on Numbers... see below

In Numbers you typically use finite size tables to group data (similar to named ranges in excel). I created a data table with fruit names in column B because I plan on using column A to identify the first entry for each name.

the first row is a header row.

A2=IF(COUNTIF($B$1:B1,B2)=0, MAX($A$1:A1)+1, "")

this is shorthand for... select cell A2, then type (or copy and paste from here) the formula:

=IF(COUNTIF($B$1:B1,B2)=0, MAX($A$1:A1)+1, "")

to fill down:

select cell A2, copy

select cells A2 thru the end of column A, paste

Now create a second table for a summary... like this:

The first row is a header row

A2=IFERROR(VLOOKUP(ROW()−1, Fruit Data::A:B, 2, 0), "")

B2=IF(A2≠"",COUNTIF(Fruit Data::B, A2),"")

to fill down:

select cells A2 and B2, copy

select cells A2 thru the end of column B, paste

Hi Cobalto,

Yes, there is a way to do this. It uses a separate index column, which you apparently would reject as a "workaround."

You can also extract the distinct values using an AppleScript, but I assume you would that a "workaround" as well.

With your stated restrictions, the simple answer to your question is "no."

Regards,

Barry

Cobalto27 wrote:

want to extract unique values

What I use to extract distinct values is this Copy Distinct Automator Service (Dropbox download).

Double-click the downloaded .workflow package to install (if necessary give permission at System Prefernces > Security & Privacy, and make sure Automator.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility).

Thereafter to use, just select the cells the cells that may include duplicate values, choose 'Copy Distinct' from your Numbers > Preferences menu, click a destination cell and command-v or Edit > Paste and Match Style. This is similar to built-in functionality in Excel. A few clicks and you're done. No messing with extra columns and formulas and all that.

SG

Cobalto27 wrote:

need to be updated manually.

A quick menu pick does the job. It leaves your document light, responsive, and uncluttered, and the results are readily sortable. In both Numbers and Excel I find that approach more productive than setting up formulas and an extra column each time. It's perhaps for this reason that Excel offers a similar ability to extract distinct values right in its user interface.

i didn't know you could use Automator inside an Numbers file.

You're not using Automator "inside" a Numbers file. You're simply adding an item to your Numbers > Services menu that after the service has been installed can be used with any Numbers file that you create. The result is somewhat similar to the Excel interface. No more need to set up extra columns and formulas each time you encounter a situation where you need to extract distinct values (which, for me, happens quite often). Give it a try! It takes less than a minute to get it going.

SG

Hi Cobalto,

Well, you did explicitly state "i'm not talking about a workaround using several columns. I'm talking about a way of fill down cells with a formula, obtaining the unique values dynamically."

And your initial post implied what you explicitly stated in the reply above: "I wanted to filter (out) complicated answers like …scripting,"

So I took you as meaning what you said, and didn't supply a solution essentially the same as what Wayne posted this morning (and as he and I and others have posted to several similar questions, expressed without the fiters, on previous occasions).

Given your expressed and implied conditions, "no" was a correct answer, although certainly neither a solution, nor, in the short term, a helpful response. Whether the last continues to hold true in the long term isn't yet determined.

Regards,

Barry

Cobalto27 wrote:

I wanted to filter complicated answers like coping and pasting manually, scripting, or reversing engineering the binary code with a hex editor. 🙂 ...and try to keep the answer inside the fill-down a formula spectrum.

Anyways, thanks for your "Simple" answer.

And further to Barry's comment. "Fill-down a formula" sounds "simple." But isn't always simple, especially if it involves setting up a complex formula and an extra column every time.

And "scripting," while often "complicated" to do from scratch, can be very "simple" for a user once the script is already written.

Extracting distinct values is, I believe, one of those clear cases where using an existing script is far more "simple" for the user. Give it a try and I think you'll see what I mean.

SG