counting unique values with 2 criteria

[paramnayak]

dear all,
I want to count the unique values satisfying 2 critera.

eg
ref no date item
2 01-02-2016 abc
3 01-02-2016 xyz
7 02-02-2016 abc
2 04-03-2016 abc
2 04-03-2016 xyz
7 05-03-2016 abc
3 07-03-2016 xyz
here ref no. and date should be unique . so total should be 6. I want to get the result with one formula.

thanks and rgds

 

[Naresh Yadav]

Dear Param

pls referred the attached file

 

[paramnayak]

dear naresh ,
thanks for your solution.
But is it possible to get the result in one formula.

 

[bines53]

Hi paramnayak,

here,
=SUMPRODUCT(--(MATCH(A2:A8&B2:B8,A2:A8&B2:B8,0)=(ROW(A2:A8)-1)))

David

 

[bosco_yip]

Or,

=SUMPRODUCT(1/COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8))

Regards
Bosco

 

[paramnayak]

thanks david and bosco. This solves my purpose