count and sum unique values

[kv.singh]

Sir,
In want to count unique values based on DOB(date of birth) through excel formula.
I have tried many excel formulas. but unable to get desired result.
unique count(unique DOB in NB sheet) in Col.F6 if C2 satisfies below mentioned contidions:-
IF(NB!A:A,"NB"and NB!M:M,C2 and NB!F:F>=A6 and NB!F:F<=&B6, nb!L:L (UNIQUE DOB)
EXPECTED ANSWER IN WRITTEN IN COL F6 AND F14

 

[shrivallabha]

Your data in column A and B shall be numerical then following array formula (to be committed by CTRL+SHIFT-ENTER) works.
=SUM(--(FREQUENCY(IF(NB!$A$2:$A$22="NB",IF(NB!$M$2:$M$22=BONOUS!$C$2,IF(NB!$F$2:$F$22>=BONOUS!A6,IF(NB!$F$2:$F$22<=BONOUS!B6,NB!$L$2:$L$22),0),0),0),IF(NB!$A$2:$A$22="NB",IF(NB!$M$2:$M$22=BONOUS!$C$2,IF(NB!$F$2:$F$22>=BONOUS!A6,IF(NB!$F$2:$F$22<=BONOUS!B6,NB!$L$2:$L$22),"a"),"a"),"a"))>0))

See attached file and column H.

 

[Peter Bartholomew]

The result should be a count of distinct DOB that match the criterion, so
= COUNT( distinctDOB )
where defined name 'distinctDOB' is given by
= UNIQUE( FILTER(Table1[DOB], filter.criterion ) )
and the 'filter.criterion' is the product of text and date criteria
= (Table1[FLAG]="NB") * (Table1[AG]=customer.code) * (Table1[DOC1]>=@start) * (Table1[DOC1]<=@end )

 

[AlanSidman]

xpost: https://www.excelforum.com/excel-fo...e-value-count-based-of-multiple-criteria.html

 

[vletm]

kv.singh
You should reread Forum Rules,

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

please.