Counting Unique Cells of Column in Excel <-- Help!!!?

Question

Hi There,

I need some excel help here. I need to count the unique # of cells in an excel spreadsheet. Here is an example:

_A | B | C | D

1dog | fed

2dog | not fed

3cat | fed

4snake | not fed

5dog | not fed

6cat | fed

I need 2 formulas:

1. that will say I have 3 unique animals in column A (dog, snake and cat)

2. which ones out of that (in #1) have been feed

Thanks!!!

Answer 1

1. use this array formula in c1

=SUM(IF(LEN(A1:A6), 1/COUNTIF($A$1:$A$6,$A$1:$A$6)))

do not just hit the usual Enter key

its an array, so you have to hit Ctrl-Shift-Enter

if you hit all 3 keys properly, you will get curly brackets { and } around your formula...you cant manually type those in.

that will give you 3

2. you just want a count of the number of time &quot;fed&quot; shows up?its possible to have higher # of fed then unique animals...

use this formula

=COUNTIF(B1:B6,&quot;fed&quot;)

this will also give you a 3 for this sample.

you should be able to change variables, and it should just work.