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
Kindly refer attached sheet and provide solution.
Sorry, no sheet attached. See yellow banner
Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks
Please update your user profile with the version of Excel that you are using.
You have not attached the workbook - instructions at the top of the page.
There ios no need to SHOUT at us!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Unable to upload file. File type is xlsx size 2.39 MB
Error message "kv.xlsx - Upload of file failed." in manage attachment.
You didn't read the instructions properly:
Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...)
Your file is too large. It is limited to 1MB. Scale it down and try again.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Sorry for Inconvenience.
Please try at F6
=COUNT(1/FREQUENCY(IF(NB!$M$2:$M$9999=$C$2,IF(NB!$A$2:$A$9999="NB",IF((NB!$F$2:$F$9999>=--A6)*(NB!$F$2:$F$9999<=--B6),NB!$L$2:$L$9999))),NB!$L$2:$L$9999))
Don't use the whole column with array formula.
Your question is not very clear to me.
Just to go started.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
BONOUS
F6=SUM(IF(FREQUENCY(IF(NB!$A$2:$A$1000=NB!$A$2,IF(NB!$M$2:$M$1000=BONOUS!$C$2,IF(NB!$F$2:$F$1000>=BONOUS!$A6,IF(NB!$F$2:$F$1000<=BONOUS!$B6,NB!$L$2:$L$1000)))),NB!$L$2:$L$1000),1))
Control+shift+enter
copy down
Last edited by CARACALLA; 04-26-2020 at 09:18 AM.
Sorry sir, I have tried both formulas but they are not giving correct result. Another xls file with detailed explanation is attached.
1 Total record for 0026628X in sheet NB >=F6(i.e. >=20190401) and <=B6(i.e. 20200331)
2 are 7 out of which one record(seria; number 7) has "ND" flag so it does not satisfy condition NB!A:A="NB"
Out of 6 records which satifies condition 1 & 2 four records(in red color) are duplicates)
so the answer in col. F6 should be 5
Simalarly answer in F14 should be 6
Kindly provide solution.
With new file
F6 =4
F14=6
BONOUS
F6=SUM(IF(FREQUENCY(IF(NB!$A$2:$A$1000=NB!$A$2,IF(NB!$M$2:$M$1000=BONOUS!$C$2,IF(NB!$F$2:$F$1000>=BONOUS!$A6,IF(NB!$F$2:$F$1000<=BONOUS!$B6,NB!$L$2:$L$1000)))),NB!$L$2:$L$1000),1))
Control+shift+enter
copy down
Last edited by CARACALLA; 04-26-2020 at 11:00 AM.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important. This is your second abuse of the forum rules. Please read the forum rules you agreed to and abide by them in the future. Continued ignoring forum rules may result in banishment. We take offenses to the rules serious.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Last edited by alansidman; 04-26-2020 at 02:52 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks