Count if

[Lindsey]

I need a formula that will count the unique text values of one column with a
specific value in another column. Here is an example:

Date Text
200811 Cat
200901 Dog
200901 Mouse
200811 Frog
200811 Cat
200901 Bird

If the date was 200811, then the unique values would be 2.
If the date was 200901, then the unique values would be 3.

Please let me know if you have any questions, thanks!

 

[Gary''s Student]

Assuming your dates are just numbers, we can use a helper column:

In C1 enter:
200811 this is the desired number for column A
In C2 enter:
=IF(A2<>C$1,"",B2) and copy down

In A1 thru C7 we see:
Date Text 200811
200811 Cat Cat
200901 Dog
200901 Mouse
200811 Frog Frog
200811 Cat Cat
200901 Bird

Note the unwanted dated are blanked out. Finally use:

=SUMPRODUCT((C2:C1500<>"")/COUNTIF(C2:C1500,C2:C1500&""))
which will display 2

 

[Lindsey]

Sorry, but each dates will never have names that are the same, yet a certain
date will have repeat of certain names. For example, 200901 will have several
repeats and so wil 200811. However, 200901 and 200811 will never have the
same names.

I am actually trying to pull this information into a table, where it will
show me the number of unique names for each date. So, creating a seperate
column everytime there is new data, the spreadsheet may become very large.

To make this more complicated, there another column between the date a text
column with 2 categories, that also needs to be a criteria along with the
date.

Date Number Text
200811 1 Cat
200901 2 Dog
200901 1 Mouse
200811 2 Frog
200811 1 Cat
200901 2 Bird

 

[Lindsey]

This is the formula I used, it worked when I only had two if conditions and
then I added a third and now it is giving me an #VALUE

=SUM(--(FREQUENCY(IF((AND($A$2:$A$50=E$1,$F$2:$F$50=$B41,$J$2:$J$50=$B$5)),MATCH($L$2:$L$50,$L$2:$L$50,0)),ROW(INDIRECT("1:"&ROWS($L$2:$L$50))))>0))