Count in column B ignoring duplicates in column a

[fishep6]

I have a formula where it counts the unique values in column A

=SUMPRODUCT((A4:A1000<>"")/COUNTIF(A4:A1000,A4:A1000&""))

Maybe this could be made better also? At present the above is an array formula


However what I am trying to do is create a formula that will count all the dates in column B based on a criteria

=COUNTIF(CSGP!b4:b1000,"<"&$C1) where C1 is a fixed date

what I want to do is to make this count in column B ignore duplicate values in column A so somehow I need something that will combine the 2 formulas but I cant work it out.

So my formula "=COUNTIF(CSGP!b4:b1000,"<"&$C1)" currently would count 6 dates that are before 01/01/1997

however what I want it to do is return the count of 4 as there are 2duplicate entries in column A "A - 01/01/1996" and "D - 19/09/1994" so I want each counted once not twice if that makes sense? Thank you in advance

Column A	Column B	Column C
A	01/01/1996	01/01/1997
A	01/01/1996
B	02/04/1995
C	06/02/2012
D	19/09/1994
D	19/09/1994
E	22/05/2017
E	22/05/2017
F	13/06/1989

<tbody>
</tbody>

 

[Ron Coderre]

Using your posted example...
Try something like this regular formula:

=SUMPRODUCT(($B$1:$B$1000<>"")*($B$1:$B$1000<$C$1)*(MATCH($A$1:$A$1000&"|"&$B$1:$B$1000,$A$1:$A$1000&"|"&$B$1:$B$1000,0)=ROW($A$1:$A$1000)))

Is that something you can work with?

 

[fishep6]

Thank you that works however I have now tried applying it to a different rang and it wont work.

My rang starts from row 4 rather than row 1 and as soon as I do that it breaks the formula.

I think it may be to do with this part of the formula which has a zero in ",0)=ROW" but I cant get it to work by changing the number

I want it to start in rows A4 and B4

=SUMPRODUCT(($B$1:$B$1000<>"")*($B$1:$B$1000<$C$1)*(MATCH($A$1:$A$1000&"|"&$B$1:$B$1000,$A$1:$A$1000&"|"&$B$1:$B$1000,0)=ROW($A$1:$A$1000)))

 

[Ron Coderre]

You need to account for the rows above your data.
Try this:

=SUMPRODUCT(($B$4:$B$1000<>"")*($B$4:$B$1000<$C$1)*(MATCH($A$4:$A$1000&"|"&$B$4:$B$1000,$A$4:$A$1000&"|"&$B$4:$B$1000,0)=ROW($A$4:$A$1000)[SIZE=5][COLOR=#ff0000]-3[/COLOR][/SIZE]))

Does that help?

 

[fishep6]

That is brilliant, thank you for all your help

 

[fishep6]

How would I tweak this to make it look between 2 dates

Currently I22 is the upper date and cell I21 is the lower date

I tried replacing the section ATKE!U4:U991<=$I$22 in the long formula below with =COUNTIFS(ATKE!U4:U991,">"&I21,ATKE!U4:U991,"<="&I22) but this brings up an error

so I want the below formula to read between date ranges in cells I21 and I22 please


=SUMPRODUCT((ATKE!U4:U991<>"")*(ATKE!U4:U991<=$I$22)*(MATCH(ATKE!$D$4:$D$991&"|"&ATKE!U4:U991,ATKE!$D$4:$D$991&"|"&ATKE!U4:U991,0)=ROW(ATKE!$D$4:$D$991)-3))

Thanks again

 

[fishep6]

Sorry...a new criteria I have now thought of if possible is I need to add another dimension into the formula as well as the data range as mention above.

At the moment the formula looks at and counts the number of unique dates in column U based on removing duplicate entries in column D and gives me a count say of 200

what I then really need to do is split this example of 200 numbers into 2 further categories based on data in column X (still with the same range X4:X991)

the split I want is how many unique values of those 200 have a date in column X versus a blank in column X

so the current formula which splits it and gets a number of say 200

will now split that answer to say 150 with a date in column X and 50 without a date if that makes sense????