Is there a formula to count unique entries in a range of cells, and possible ignore certain predefined entries

Mike,

Is something like this what you are looking for?

I think you need an extra "counter" column.

The formula in C2, copied down, is:

=IF(COUNTIFS($B$2:B2,B2,$B$2:B2,"<>" & "off")=1,1,0)

A2 in the Count table, the distinct count, is the sum of the "counter" column.

=SUM(Table 1::C)

SG

In the description above, when you say A5 to count unique entries, you really mean the TOTAL_CASES column right? That is what I get from your table, TOTAL_CASES (I'll call it column C) cells are adding 1 each time it comes to a new name.

Couple different ways to do this depending on how the NAME column is setup.

In your example, all the duplicate names are next to each other. If this is always the case (or if you can sort the NAME column and don't care it will mess up the date column), then Option A. Otherwise, Option B, which is more complicated to explain.

Option A:

Put this formula in C2 and fill down.

=IF(OR(B2="off",B2="vacation",B2="",B2=B1),C1,SUM(C1,1))

Says if B2 is off, vacation, empty, or equal to the cell above it, then don't add 1. Otherwise, add 1.

Option B:

Put this formula in C2 and fill down.

=COUNTIF(B,B2)

Says count how many duplicates for each name.

Put this formula in D2 and fill down.

=COUNTIF(C,ROW()−1)÷(ROW()−1)

Says.. well.. I need to explain with specifics.

Cell D2 says how many names have no duplicates.

In your table, 5 (Roxy, Brad, Linda, off, Julie), then 5/1 = 5 unique names.

Cell D3 says how many names have exactly 1 duplicate and divide that by 2.

In your table, 4 (John, John, Huang, Huang), then 4/2 = 2 unique names.

Cell D4 says how many names have exactly 2 duplicates and divide that by 3.

In your table 0.

etc...

Add a footer row to the table and put this in the footer cell for column D.

=SUM(D)−IF(COUNTIF(B,"off"),1,0)−IF(COUNTIF(B,"vacation"),1,0)

Says SUM up the unique names, but minus 1 if there are ANY number of offs, and minus 1 if there are ANY number of vacations.

Disclaimer. I 'think' this works.

My solution appears to work. I tried just about everything. But, looks like SGIII had a simpler solution. Nice one.

Message was edited by: ronniefromcalifornia

Hi Mike,

There are four names that are unique within your sample (not counting "off"): Roxy, Linda, Bradley and Julie. But I suspect you want a count of distinct names (6, again not counting "off".

Here's an example, using a method similar to that proposed by SG, but that also constructs an index useful in retrieving the list of distinct names for use in a separate table:

Column C of Data contains the count. It uses this formula, enterec in C2 and filled down to C11:

=IF(OR(B="off",B="vacation",COUNTIF($B$2:B2,B)>1),"",MAX($C$1:C1)+1)

If the total is needed elsewhere on the table, it can be retrieved using the formula in D2:

=MAX(C)

The index to names created in column C can be used to retrieve those names in a Summary table, using a LOOKUP formula such as the one in A2, and filled down:

=IF(ROW()-1>MAX(Data :: C),"",LOOKUP(ROW()-1,Data :: C,Data :: B))

The IF part of this formula is used to shut it off when the last name has been retrieved, preventing LOOKUP from repeating the last value retrieved in all rows below row 7.

Other data can be retrieved from the same row of the data table, or as done here, can be calculated from data on that table using formulas referencing that data, such as this one, entered in B2 and filled down:

=IF(LEN(A)<1,"",COUNTIF(Data :: B,A))

As above, the IF condition is used to keep the appearance of blank cells where there is no data to be retrieved of=r calculated.

Regards,

Barry

@Ronnie and @Barry

Learned a lot from your posts. Like you I'm not sure if this is what the OP wants, but I often find myself in a situation where I want to extract and list distinct values. Barry's index column approach is ingenious and allows you to stick to Numbers proper. If you want to "cheat" and use AppleScript (and you have Numbers 3.1) I've found this Automator Service (Dropbox download) convenient. All I have to do is select the cells I want to extract duplicates form, make a menu pick (could even be a keyboard shortcut), and paste (command-v) the list of distinct values where wanted. On my machine it looks like this:

More details are in this thread in case you want to check it out before downloading. (If you download and get a warning when you double-click to install, go to System Preferences > Security & Privacy and click 'Download anyway'.)

SG

If I'm understanding correctly, something like this should work:

=COUNTIF(D$3:D3,"Y"), copied down.

This assumes the Ys and Ns are in column D.

SG

If you are talking about the 'Cases Video Obtained' column in your second table, I think this works:

=IF(AND(C2=1,D2="Y"),MAX(G$1:G1)+1,MAX(G$1:G1)) in G2, copied down.

However, it depends on your 'hidden counter' column. Shouldn't that be 1 for Vicki?

SG

Ah, I see. The hidden counter is 1 if that person is not already listed somewhere.

So why do you want the Cases Video Obtained to increase with the second listing of Vicki? Is that because it's a new case?

If so, you may find a Case ID column will be useful. Then you could set up another hidden counter. Or revisit Barry's approach above.

If it is indeed because the second listing of Vicki is a new case, then maybe you could add the column and post again, and someone will be able to help you get what you want.

SG

Hi Mike,

Here's my example. I've left out data not germane to the count in column G.

I don't see a use for row 2, so I've removed it. If you need that row for some purpose, just change the 1 in each of the cell references 2, and place both the formulas in row 3.

E2: =IF(D="Y",MAX($E$1:E1)+1,MAX($E$1:E1))

H2: =IF(COUNTIFS($B$2:B2,B,$D$2:D2,"Y")=1,MAX($H$1:H1)+1,MAX($H$1:H1))

Regards,

Barry