Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting unique values of text
Hi,
Column A is referencing unique text values from another spreadsheet which is where the "FALSE" comes from. How do I count how many A's are in column B but only refer to unique names in column A? The other problem is, this is a template, so column A is going to change completely. So is it possible to count the number of A's in column B that don't equal "FALSE" in column A? col. A col. B Jim A FALSE A Pam B David A FALSE A I've seen similar threads but I couldn't find any that involved text. Please help!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting unique values of text
Try this array formula** :
=COUNT(1/FREQUENCY(IF(ISTEXT(A2:A6)*(B2:B6="A"),MATCH(A2:A6 ,A2:A6,0)),ROW(A2:A6)-MIN(ROW(A2:A6))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "jake" wrote in message ... Hi, Column A is referencing unique text values from another spreadsheet which is where the "FALSE" comes from. How do I count how many A's are in column B but only refer to unique names in column A? The other problem is, this is a template, so column A is going to change completely. So is it possible to count the number of A's in column B that don't equal "FALSE" in column A? col. A col. B Jim A FALSE A Pam B David A FALSE A I've seen similar threads but I couldn't find any that involved text. Please help!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting unique values of text
For this:
.. count the number of A's in column B that don't equal "FALSE" in column A? Try: =SUMPRODUCT((A1:A5<FALSE)*(B1:B5="A")) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting unique values of text
You are some kind of sorcerer!! Perfect, thank you.
"Max" wrote: For this: .. count the number of A's in column B that don't equal "FALSE" in column A? Try: =SUMPRODUCT((A1:A5<FALSE)*(B1:B5="A")) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting unique values of text
Glad it worked ok for you.
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "jake" wrote in message ... You are some kind of sorcerer!! Perfect, thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values | Excel Worksheet Functions | |||
Counting unique values | Excel Discussion (Misc queries) | |||
counting unique items(values or text) | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions |