Count unique values using Frequency, Match, ISNUMBER, and SEARCH

[flower8330]

Hi,
I want to count the unique values in column A. Column B contains various text values. I want to count all unique values of column A that have a column B that contains "2003" within the text value.

Example data:

Column A	Column B
Server 1	Windows 2000
Server 2	Windows 2003
Server 2	Windows 2003
Server 3	Windows 2003 enterprise server

<TBODY>
</TBODY>

I need a formula that will return the value 2 (essentially counting Server 2 and Server 3).


If have the formulas below, but don't know how to combine them.

=SUM(IF(FREQUENCY(MATCH(A1:A4,A1:A4,0),MATCH(A1:A4,A1:A4,0))>0,1))

=ISNUMBER(SEARCH("2003",A:A))


Also, I would like to know if there is a way to allow for the range to be bigger than the data since the number of rows will grow over time.
I tried using a larger end range, but the funtion threw an error.
=SUM(IF(FREQUENCY(MATCH(A1:A4,A1:A10,0),MATCH(A1:A4,A1:A10,0))>0,1))

I'm using Excel 2010 on Win 7

 

[Marcelo Branco]

Welcome to Mr Excel

Try

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(2003,B1:B4)),MATCH(A1:A4,A1:A4,0)),ROW(A1:A4)-ROW(A1)+1),1))
Ctrl+Shift+Enter

Hope this helps

M.

 

[flower8330]

Welcome to Mr Excel

Try

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(2003,B1:B4)),MATCH(A1:A4,A1:A4,0)),ROW(A1:A4)-ROW(A1)+1),1))
Ctrl+Shift+Enter

Hope this helps

M.

How can I pick apart this formula to see if it's working correctly?
the formula returns a value of 628, but when i filter the data manually and remove the duplicates I get 536