• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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 )
 

vletm

Excel Ninja
kv.singh
You should reread Forum Rules,
please.
 
Top