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
Awaiting your fast feedback
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
 
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
Awaiting your fast feedback
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top