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

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

Countif (like no other)
 

This is an ARRAY formula that should be entered using ctrl+shift+enter
=COUNT(IF((A1:D2/A445)*(A1:D2/A4<55),A1:D2))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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