LinkBack URL
About LinkBacks
If I have range of data made up of different states, how do I easily count how many different states are in that range?
For Instance:
NY
PA
NY
NY
VA
NJ
NJ
IL
What formula do I use so that it equals 5?
If I have range of data made up of different states, how do I easily count how many different states are in that range?
For Instance:
NY
PA
NY
NY
VA
NJ
NJ
IL
What formula do I use so that it equals 5?
One way...
=SUM(1/COUNTIF(A1:A8,A1:A8))
IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually
HTH
Regards, Jeff
Hi,
If interested, here's another 'non-array' alternative approach (ie: you won't need to press CTRL+SHIFT+ENTER keys together to activate the formula to generate the desired result):
=SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks