# count and sum unique values

#### kv.singh

##### New Member
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

#### Attachments

• 14.7 KB Views: 8

#### shrivallabha

##### Excel Ninja
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.

#### Attachments

• 15.4 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
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 )

kv.singh