Counting Values

Posted by Lewis on May 07, 2001 8:34 PM

Hey guys,

I want a cell that counts all the cells above it but it only counts each value once... (there is text in the cells that are being counted)

so basically if I have AAABBCCCCCDD it would equal 4

I know that someone posted a reply last time I poseted this question but I tried it and couldn't seem to get it to work

maybe I'm just stupid and need it explaining to me

thanx

-Lewis

Posted by Ian G on May 07, 2001 8:59 PM

Hi Lewis
I looked at Jaime's answer to your previous post and altho it worked for me I came up with something a bit simpler.
Assuming all you want to do is count A's in A1:A10, enter the following into A11: =COUNTIF(A1:A10,"A"). This doesn't have to be array entered ie just type/paste it in and hit enter.
Cheers
Ian G

Posted by Ian G on May 07, 2001 9:12 PM

sorry about that
looking at jaime's formula again i couldnt't see why it wouldn't work. when you say you couldn't get it to work, did it return the #DIV/0! error message? if so it probably means you didn't press ctrl + shift + enter keys at the same time instead of just the enter key to to enter the formula. beyond that i don't know.
Ian G

Posted by Ian G on May 07, 2001 9:15 PM

sorry about that
Looking at jaime's formula again i couldnt't see why it wouldn't work. when you say you couldn't get it to work, did it return the #DIV/0! error message? if so it probably means you didn't press ctrl + shift + enter keys at the same time instead of just the enter key to to enter the formula. beyond that i don't know.
Ian G

Posted by Aladin Akyurek on May 07, 2001 10:34 PM

The following refs explain what the array formulas are.

See

http://www.emailoffice.com/excel/arrays-bobumlas.html

for an explanation how the array-formula

=SUM(1/COUNTIF(A1:A12,A1:A12)) [ due to D. Haeger, I believe ]

works. By the way, this one requires that there are no blank cells in the range that is given to it.

Pearson has more on counting unique entries (including a workbook: UNIQUE.XLS) at

http://www.cpearson.com/excel/duplicat.htm#CountingUnique