Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
Please help me. I am trying to create a function that will count if cell
values are between 60 and 180. The funtion below works but I can't get the AND logic in the function. =COUNTIF(AE740:AE863,"180") I have tried this but it does not work: =COUNTIF(AE740:AE863,"60 AND <180") I have also tried this: =COUNTIF((AE740:AE863,"60) AND (AE740:AE863, "<180")) Thanks, Misty -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
=COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180")
you might want to change the second to = if you want to exclude 180. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message news:73effad7f72b7@uwe... Please help me. I am trying to create a function that will count if cell values are between 60 and 180. The funtion below works but I can't get the AND logic in the function. =COUNTIF(AE740:AE863,"180") I have tried this but it does not work: =COUNTIF(AE740:AE863,"60 AND <180") I have also tried this: =COUNTIF((AE740:AE863,"60) AND (AE740:AE863, "<180")) Thanks, Misty -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
I will try it thanks and thanks for the tip!
Bob Phillips wrote: =COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180") you might want to change the second to = if you want to exclude 180. Please help me. I am trying to create a function that will count if cell values are between 60 and 180. [quoted text clipped - 9 lines] Thanks, Misty -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
What doe the subtraction sign do in this function? I just notices that the
are facing the same way and I was thinking that it had something to do with it. I can't figure out if that is between 60 and 180 or both greater than 60 and 180. Bob Phillips wrote: =COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180") you might want to change the second to = if you want to exclude 180. Please help me. I am trying to create a function that will count if cell values are between 60 and 180. [quoted text clipped - 9 lines] Thanks, Misty -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
The first formula will count all occurrences greater than 60 so if you want
to include 60 use =60. Anyway, let's say that it will return 10 now the second will count all occurrences greater than 180, assume that there are 4, the difference will be 10-4 which is 6 which is in fact all the occurrences between 60 and 180. My guess is that you want to include 60 and should then use =60, if not use 60 and if you don't want to include 180 change 180 to =180 in the second formula The reason is that you can't use COUNTIF in one fell swoop with AND It can also be done by using =SUMPRODUCT(--(Range=60),--(Range<=180)) which is probably more in line with what you thought it would look like -- Regards, Peo Sjoblom "Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote in message news:73f14f8d3af7f@uwe... What doe the subtraction sign do in this function? I just notices that the are facing the same way and I was thinking that it had something to do with it. I can't figure out if that is between 60 and 180 or both greater than 60 and 180. Bob Phillips wrote: =COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180") you might want to change the second to = if you want to exclude 180. Please help me. I am trying to create a function that will count if cell values are between 60 and 180. [quoted text clipped - 9 lines] Thanks, Misty -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
Bob's formula says count all cells greater than 60 then subtract all cells
greater than 180 This will return a count of cells from 61 to 180 You said "between 60 and 180" so that should be 60.0000000001 to 179.999999999999 If you want 60 to 180...................... Change the operators and numbers to suit your needs. e.g. =60 or =180 Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 17:53:35 GMT, "Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote: What doe the subtraction sign do in this function? I just notices that the are facing the same way and I was thinking that it had something to do with it. I can't figure out if that is between 60 and 180 or both greater than 60 and 180. Bob Phillips wrote: =COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180") you might want to change the second to = if you want to exclude 180. Please help me. I am trying to create a function that will count if cell values are between 60 and 180. [quoted text clipped - 9 lines] Thanks, Misty |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
Ok, thanks for the clarification! I kept second guessing the numbers. I
appreciate both of you. -Misty Gord Dibben wrote: Bob's formula says count all cells greater than 60 then subtract all cells greater than 180 This will return a count of cells from 61 to 180 You said "between 60 and 180" so that should be 60.0000000001 to 179.999999999999 If you want 60 to 180...................... Change the operators and numbers to suit your needs. e.g. =60 or =180 Gord Dibben MS Excel MVP What doe the subtraction sign do in this function? I just notices that the are facing the same way and I was thinking that it had something to do with [quoted text clipped - 10 lines] Thanks, Misty -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
It is always debatable when an OP says between whether that should include
the specified numbers or exclude them <bg. .. The only guarantee is that whatever you pick, it will be wrong <ebg. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Bob's formula says count all cells greater than 60 then subtract all cells greater than 180 This will return a count of cells from 61 to 180 You said "between 60 and 180" so that should be 60.0000000001 to 179.999999999999 If you want 60 to 180...................... Change the operators and numbers to suit your needs. e.g. =60 or =180 Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 17:53:35 GMT, "Mitchell_Collen via OfficeKB.com" <u33726@uwe wrote: What doe the subtraction sign do in this function? I just notices that the are facing the same way and I was thinking that it had something to do with it. I can't figure out if that is between 60 and 180 or both greater than 60 and 180. Bob Phillips wrote: =COUNTIF(AE740:AE863,"60")-COUNTIF(AE740:AE863,"180") you might want to change the second to = if you want to exclude 180. Please help me. I am trying to create a function that will count if cell values are between 60 and 180. [quoted text clipped - 9 lines] Thanks, Misty |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with AND logic
Bob's formula says count all cells greater than 60 then subtract all cells
greater than 180 This will return a count of cells from 61 to 180 You said "between 60 and 180" so that should be 60.0000000001 to 179.999999999999 If you want 60 to 180...................... Change the operators and numbers to suit your needs. e.g. =60 or =180 I think you meant to type 180 (in order to include the 180 in the range). Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Using Multiple Logic Tests | Excel Worksheet Functions | |||
Logic of this formula =SUMPRODUCT((A1:A4<"")/COUNTIF(A1:A4,A1:A4& | Excel Discussion (Misc queries) | |||
COUNTIF with Logic? | Excel Worksheet Functions | |||
If Then logic not enough | Excel Discussion (Misc queries) | |||
Countif with AND logic | Excel Worksheet Functions |