ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   How to count letter B based on data in other columns (https://www.excelbanter.com/excel-worksheet-functions/231587-how-count-letter-b-based-data-other-columns.html)

Mero
How to count letter B based on data in other columns
 
Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some records
are duplicated. If I count letter "B", I will get 4 but actually they are
only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero

Bernie Deitrick
How to count letter B based on data in other columns
 
Mero,

Again, array enter:

=SUM(N(FREQUENCY(IF((rngF<"00.00.0000")*(rngJ="B" ),MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",) )0))

Though you may need to use something like this if the dates are true dates
and not strings:

=SUM(N(FREQUENCY(IF((TEXT(rngF,"dd.mm.yyyy")<"00. 00.0000")*(rngJ="B"),MATCH(rngD&"",rngD&"",)),MATC H(rngD&"",rngD&"",))0))

HTH,
Bernie
MS Excel MVP


"Mero" wrote in message
...
Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on
unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some
records
are duplicated. If I count letter "B", I will get 4 but actually they
are
only 2 records for letter "B" as 8020249409 and 8020249409 are
duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero

Mero
Please help: How to count letter B based on data in other columns
 
Please support me in the below issue ASAP

"Mero" wrote:

Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some records
are duplicated. If I count letter "B", I will get 4 but actually they are
only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero

Marso
How to count letter B based on data in other columns
 

Thanks a million.....it worked perfectly


--
Marso
------------------------------------------------------------------------
Marso's Profile: http://www.thecodecage.com/forumz/member.php?userid=339
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98906

Teethless mama
How to count letter B based on data in other columns
 
Assuming Column F contain real Excel dates

=SUM(N(FREQUENCY(IF((rngJ="B")*(rngF0),MATCH(rngD &"",rngD&"",)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some records
are duplicated. If I count letter "B", I will get 4 but actually they are
only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero

Mero
How to count letter B based on data in other columns
 
Hi Teethless,
Thanks for yourr support bu the below formula count the letter B with date
00.00.0000.....I dont know why.

"Teethless mama" wrote:

Assuming Column F contain real Excel dates

=SUM(N(FREQUENCY(IF((rngJ="B")*(rngF0),MATCH(rngD &"",rngD&"",)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some records
are duplicated. If I count letter "B", I will get 4 but actually they are
only 2 records for letter "B" as 8020249409 and 8020249409 are duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero

Mero
How to count letter B based on data in other columns
 
Many Thanks Bernie! it is working

"Bernie Deitrick" wrote:

Mero,

Again, array enter:

=SUM(N(FREQUENCY(IF((rngF<"00.00.0000")*(rngJ="B" ),MATCH(rngD&"",rngD&"",)),MATCH(rngD&"",rngD&"",) )0))

Though you may need to use something like this if the dates are true dates
and not strings:

=SUM(N(FREQUENCY(IF((TEXT(rngF,"dd.mm.yyyy")<"00. 00.0000")*(rngJ="B"),MATCH(rngD&"",rngD&"",)),MATC H(rngD&"",rngD&"",))0))

HTH,
Bernie
MS Excel MVP


"Mero" wrote in message
...
Hello...I did ask the below question 3 days ago and I got answer from you.
Now I need to add something to my below question

In the below formula you gave me, we count letter B based on unique value
among duplicates in column D.
Now, I added another column F which contains dates.
I need to count letter B based on unique value in column D and that has no
date like 00.00.0000 in column F.
Please See below 3 columns, the result should be only 1 B

Hope I've explained my problem
I dont know how to thank you for this support
Awaiting your fast feedback
Mero

"Teethless mama" wrote:

=SUM(N(FREQUENCY(IF(rngJ="B",MATCH(rngD&"",rngD&"" ,)),MATCH(rngD&"",rngD&"",))0))

ctrl+shift+enter, not just enter


"Mero" wrote:

Hello, Need to know how to count the letter "B" in column J based on
unique
values among duplicates in another column.

Column D Column J Column F
5020598285 A
5020598286 A
5020598287 A
5020598288 A
5020598563 A
8020249197 C
8020249198 C
8020249409 B 00.00.0000
8020249409 B 00.00.0000
8020249450 B 13.01.2009
8020249450 B 13.01.2009
5020598429 A
5020598707 A

If you have a look at records in column D, you will find that some
records
are duplicated. If I count letter "B", I will get 4 but actually they
are
only 2 records for letter "B" as 8020249409 and 8020249409 are
duplicated.
I need a function to count letter "B" without duplication.

Hope I've explained my problem.
Your fast support is really appreciated.

Many Thanks
Mero


All times are GMT +1. The time now is 09:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com