COUNTIF, only counting once if both cells contain the data
 

I have either a Y or N in cells based on two different things that happened
for the day (which explains why there are 7 COUNTIF statements, they all
pertain to a single day of the week).

What I am trying to do is to add up the number of days that EITHER one or
the other happened. I used the formula below:

=COUNTIF(C6:D6,"Y")+COUNTIF(F6:G6,"Y")+COUNTIF(I6: J6,"Y")+COUNTIF(L6:M6,"Y")+COUNTIF(O6:P6,"Y")+COUN TIF(R6:S6,"Y")+COUNTIF(U6:V6,"Y")&" / 7"

However, seeing as how I got a result of 8 / 7 for some days (in which on a
single day BOTH things happened), I realized the problem with the function,
as it is simply counting "Y's". I need to be able to count whether there's a
Y in EITHER one in the range (which pertains to a single day).

I tried to solve this with corresponding statements of -COUNTIF(C6, $D$6)
(etc), but for those in which there were NO Y's for the week, it returned a
negative number because it subtracted the fact that the N's are the same.

I'm stumped. Any help would be greatly appreciated, as I have 150 rows each
on 3 different sheets and manually adding up all these would take me a long
time (also, I plan to use this a great deal in the future, so it benefits me
for a great deal of time), so I'm hoping to get a response in the next couple
hours that will help me.

Thank you.

COUNTIF, only counting once if both cells contain the data
 

Replace terms
MIN(1,COUNTIF(C6:D6,"Y"))
--- OR ---
OR(C6="Y",D6="Y")
You will need to use =--(OR(C6="Y",D6="Y")test it on one pair but
=OR(C6="Y",D6="Y")+OR(F6="Y",G6="Y")..... will work

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Navy Luke" <Navy wrote in message
...
I have either a Y or N in cells based on two different things that happened
for the day (which explains why there are 7 COUNTIF statements, they all
pertain to a single day of the week).

What I am trying to do is to add up the number of days that EITHER one or
the other happened. I used the formula below:

=COUNTIF(C6:D6,"Y")+COUNTIF(F6:G6,"Y")+COUNTIF(I6: J6,"Y")+COUNTIF(L6:M6,"Y")+COUNTIF(O6:P6,"Y")+COUN TIF(R6:S6,"Y")+COUNTIF(U6:V6,"Y")&"
/ 7"

However, seeing as how I got a result of 8 / 7 for some days (in which on
a
single day BOTH things happened), I realized the problem with the
function,
as it is simply counting "Y's". I need to be able to count whether
there's a
Y in EITHER one in the range (which pertains to a single day).

I tried to solve this with corresponding statements of -COUNTIF(C6, $D$6)
(etc), but for those in which there were NO Y's for the week, it returned
a
negative number because it subtracted the fact that the N's are the same.

I'm stumped. Any help would be greatly appreciated, as I have 150 rows
each
on 3 different sheets and manually adding up all these would take me a
long
time (also, I plan to use this a great deal in the future, so it benefits
me
for a great deal of time), so I'm hoping to get a response in the next
couple
hours that will help me.

Thank you.

COUNTIF, only counting once if both cells contain the data
 

Bernard, thanks a bunch. I didn't realize that a simple OR function would
count them correctly. Thank you for the quick reply, this is data we're
briefing to an Admiral, I wanted to make sure to have the data correct.

Luke

"Bernard Liengme" wrote:

Replace terms
MIN(1,COUNTIF(C6:D6,"Y"))
--- OR ---
OR(C6="Y",D6="Y")
You will need to use =--(OR(C6="Y",D6="Y")test it on one pair but
=OR(C6="Y",D6="Y")+OR(F6="Y",G6="Y")..... will work

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Navy Luke" <Navy wrote in message
...
I have either a Y or N in cells based on two different things that happened
for the day (which explains why there are 7 COUNTIF statements, they all
pertain to a single day of the week).

What I am trying to do is to add up the number of days that EITHER one or
the other happened. I used the formula below:

=COUNTIF(C6:D6,"Y")+COUNTIF(F6:G6,"Y")+COUNTIF(I6: J6,"Y")+COUNTIF(L6:M6,"Y")+COUNTIF(O6:P6,"Y")+COUN TIF(R6:S6,"Y")+COUNTIF(U6:V6,"Y")&"
/ 7"

However, seeing as how I got a result of 8 / 7 for some days (in which on
a
single day BOTH things happened), I realized the problem with the
function,
as it is simply counting "Y's". I need to be able to count whether
there's a
Y in EITHER one in the range (which pertains to a single day).

I tried to solve this with corresponding statements of -COUNTIF(C6, $D$6)
(etc), but for those in which there were NO Y's for the week, it returned
a
negative number because it subtracted the fact that the N's are the same.

I'm stumped. Any help would be greatly appreciated, as I have 150 rows
each
on 3 different sheets and manually adding up all these would take me a
long
time (also, I plan to use this a great deal in the future, so it benefits
me
for a great deal of time), so I'm hoping to get a response in the next
couple
hours that will help me.

Thank you.

COUNTIF, only counting once if both cells contain the data
 

Hi

Look at this example:

=--(OR(C6="Y",D6="Y"))+--(OR(F6="Y",G6="Y"))

Hopes it helps.

Regards,
Per

On 2 Dec., 20:27, Navy Luke <Navy
wrote:
I have either a Y or N in cells based on two different things that happened
for the day (which explains why there are 7 COUNTIF statements, they all
pertain to a single day of the week). *

What I am trying to do is to add up the number of days that EITHER one or
the other happened. *I used the formula below:

=COUNTIF(C6:D6,"Y")+COUNTIF(F6:G6,"Y")+COUNTIF(I6: J6,"Y")+COUNTIF(L6:M6,"Y"*)+COUNTIF(O6:P6,"Y")+COU NTIF(R6:S6,"Y")+COUNTIF(U6:V6,"Y")&" / 7"

However, seeing as how I got a result of 8 / 7 for some days (in which on a
single day BOTH things happened), I realized the problem with the function,
as it is simply counting "Y's". *I need to be able to count whether there's a
Y in EITHER one in the range (which pertains to a single day). *

I tried to solve this with *corresponding statements of -COUNTIF(C6, $D$6)
(etc), but for those in which there were NO Y's for the week, it returned a
negative number because it subtracted the fact that the N's are the same.

I'm stumped. *Any help would be greatly appreciated, as I have 150 rows each
on 3 different sheets and manually adding up all these would take me a long
time (also, I plan to use this a great deal in the future, so it benefits me
for a great deal of time), so I'm hoping to get a response in the next couple
hours that will help me.

Thank you.

COUNTIF, only counting once if both cells contain the data
 

Do you need the double unary minus operators, Per? Doesn't the plus
operation coerce the conversion from boolean to number?
Wouldn't =(OR(C6="Y",D6="Y"))+(OR(F6="Y",G6="Y")) do the job?
--
David Biddulph

"Per Jessen" wrote in message
...
Hi

Look at this example:

=--(OR(C6="Y",D6="Y"))+--(OR(F6="Y",G6="Y"))

Hopes it helps.

Regards,
Per

On 2 Dec., 20:27, Navy Luke <Navy
wrote:
I have either a Y or N in cells based on two different things that
happened
for the day (which explains why there are 7 COUNTIF statements, they all
pertain to a single day of the week).

What I am trying to do is to add up the number of days that EITHER one or
the other happened. I used the formula below:

=COUNTIF(C6:D6,"Y")+COUNTIF(F6:G6,"Y")+COUNTIF(I6: J6,"Y")+COUNTIF(L6:M6,"Y"*)+COUNTIF(O6:P6,"Y")+COU NTIF(R6:S6,"Y")+COUNTIF(U6:V6,"Y")&"
/ 7"

However, seeing as how I got a result of 8 / 7 for some days (in which on
a
single day BOTH things happened), I realized the problem with the
function,
as it is simply counting "Y's". I need to be able to count whether there's
a
Y in EITHER one in the range (which pertains to a single day).

I tried to solve this with corresponding statements of -COUNTIF(C6, $D$6)
(etc), but for those in which there were NO Y's for the week, it returned
a
negative number because it subtracted the fact that the N's are the same.

I'm stumped. Any help would be greatly appreciated, as I have 150 rows
each
on 3 different sheets and manually adding up all these would take me a
long
time (also, I plan to use this a great deal in the future, so it benefits
me
for a great deal of time), so I'm hoping to get a response in the next
couple
hours that will help me.

Thank you.

COUNTIF, only counting once if both cells contain the data
 

Hi David

You are right, I just couldn't make i work without at first attemt.
Don't know why :-(

Regards,
Per

On 2 Dec., 21:53, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Do you need the double unary minus operators, Per? *Doesn't the plus
operation coerce the conversion from boolean to number?
Wouldn't =(OR(C6="Y",D6="Y"))+(OR(F6="Y",G6="Y")) do the job?
--
David Biddulph