COUNTIF, only counting once if both cells contain the data

N

Navy Luke

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")+COUNTIF(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.
 
B

Bernard Liengme

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
 
N

Navy Luke

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
 
P

Per Jessen

Hi

Look at this example:

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

Hopes it helps.

Regards,
Per
 
D

David Biddulph

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

Hi

Look at this example:

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

Hopes it helps.

Regards,
Per
 
P

Per Jessen

Hi David

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

Regards,
Per
 

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