Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Cells with same data | Excel Discussion (Misc queries) | |||
Counting Data from Another Worksheet - Countif | Excel Worksheet Functions | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
Counting cells with data | Excel Discussion (Misc queries) | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions |