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

M

#### Mero

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
Mero

B

#### Bernie Deitrick

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&"",)),MATCH(rngD&"",rngD&"",))>0))

HTH,
Bernie
MS Excel MVP

B

#### Bernie Deitrick

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&"",)),MATCH(rngD&"",rngD&"",))>0))

HTH,
Bernie
MS Excel MVP

M

#### Mero

Please support me in the below issue ASAP

Mero said:
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
Mero

T

#### Teethless mama

Assuming Column F contain real Excel dates

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

ctrl+shift+enter, not just enter

M

#### Mero

Hi Teethless,
Thanks for yourr support bu the below formula count the letter B with date
00.00.0000.....I dont know why.

M

#### Mero

Many Thanks Bernie! it is working

Bernie Deitrick said:
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&"",)),MATCH(rngD&"",rngD&"",))>0))

HTH,
Bernie
MS Excel MVP