count if unique value only based on date

[ADAMC]

Hi all,

Thanks in advance as always for any help.

Is it possible to say in a formula:

Count all the unique values in column B if the date in column H is less than today date?

Column B contains an invoice number but can appear many times in the column. Column H is the processing date formatted DD/MM/YYYY I only need to know the number of unique invoices older than todays date (The ones I need to deal with)

Thanks!

 

[Chameleon64]

You could add a helper column I which checks for duplicate invoices with dates before today's date. I've assumed today's date is in A1 and your data starts in row 2. Copy the following formula down column I, starting in I2:

=IF(COUNTIFS($B$2:$B2,B2,$H$2:$H2,"<"&$A$1)>1,"Duplicate","Unique")


The number of unique invoices before today's date is then:

=COUNTIFS($H:$H,"<"&A1,$I:$I,"Unique")