Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif (like no other)
I looked through the extensive no. of posts on the countif function and
believe that my issue is truly exceptional (but then again maybe not). I need to count the number of cells that are out of a specific range when the number in that cell is divided by the number in another cell. Example: count the number of cells in range A1:D2 where the number in that cell divided by the number in A4 is 55 and <45. A B C D row 1 150 200 156 250 row 2 141 150 310 408 row 4 3 in this example the answer should be 4. So far my formula looks like this: SUM(COUNTIF(C20:O67,"<45"),COUNTIF(C20:O67,"55")) but that does not account for dividing the numbers by A4. Hope this is slightly clearer than mud. Thanks for any help. Holly |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif (like no other)
make a "helper column" where you divide the number first then countif on that
column. "hnyb1" wrote: I looked through the extensive no. of posts on the countif function and believe that my issue is truly exceptional (but then again maybe not). I need to count the number of cells that are out of a specific range when the number in that cell is divided by the number in another cell. Example: count the number of cells in range A1:D2 where the number in that cell divided by the number in A4 is 55 and <45. A B C D row 1 150 200 156 250 row 2 141 150 310 408 row 4 3 in this example the answer should be 4. So far my formula looks like this: SUM(COUNTIF(C20:O67,"<45"),COUNTIF(C20:O67,"55")) but that does not account for dividing the numbers by A4. Hope this is slightly clearer than mud. Thanks for any help. Holly |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif (like no other)
Your criteria assumption is incorrect, the numbers cannot be both greater
than 55 AND less than 45, you might mean greater than 55 OR less than 45, if so =SUMPRODUCT(--((A1:D2/A455)+(A1:D2/A4<45)0),--(A1:D2<"")) to guard against empty cells who are counted as zero, if there can be no empty cells =SUMPRODUCT(--((A1:D2/A455)+(A1:D2/A4<45)0)) I haven't really tested it except for the values in your example -- Regards, Peo Sjoblom "hnyb1" wrote in message ... I looked through the extensive no. of posts on the countif function and believe that my issue is truly exceptional (but then again maybe not). I need to count the number of cells that are out of a specific range when the number in that cell is divided by the number in another cell. Example: count the number of cells in range A1:D2 where the number in that cell divided by the number in A4 is 55 and <45. A B C D row 1 150 200 156 250 row 2 141 150 310 408 row 4 3 in this example the answer should be 4. So far my formula looks like this: SUM(COUNTIF(C20:O67,"<45"),COUNTIF(C20:O67,"55")) but that does not account for dividing the numbers by A4. Hope this is slightly clearer than mud. Thanks for any help. Holly |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif (like no other)
Thank you all for your help!
"Peo Sjoblom" wrote: Your criteria assumption is incorrect, the numbers cannot be both greater than 55 AND less than 45, you might mean greater than 55 OR less than 45, if so =SUMPRODUCT(--((A1:D2/A455)+(A1:D2/A4<45)0),--(A1:D2<"")) to guard against empty cells who are counted as zero, if there can be no empty cells =SUMPRODUCT(--((A1:D2/A455)+(A1:D2/A4<45)0)) I haven't really tested it except for the values in your example -- Regards, Peo Sjoblom "hnyb1" wrote in message ... I looked through the extensive no. of posts on the countif function and believe that my issue is truly exceptional (but then again maybe not). I need to count the number of cells that are out of a specific range when the number in that cell is divided by the number in another cell. Example: count the number of cells in range A1:D2 where the number in that cell divided by the number in A4 is 55 and <45. A B C D row 1 150 200 156 250 row 2 141 150 310 408 row 4 3 in this example the answer should be 4. So far my formula looks like this: SUM(COUNTIF(C20:O67,"<45"),COUNTIF(C20:O67,"55")) but that does not account for dividing the numbers by A4. Hope this is slightly clearer than mud. Thanks for any help. Holly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif? | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |