Countif (like no other)

[Guest]

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 A12 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

 

[Guest]

make a "helper column" where you divide the number first then countif on that
column.

 

[Don Guillett]

This is an ARRAY formula that should be entered using ctrl+shift+enter
=COUNT(IF((A12/A4>45)*(A12/A4<55),A12))

 

[Peo Sjoblom]

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(--((A12/A4>55)+(A12/A4<45)>0),--(A12<>""))


to guard against empty cells who are counted as zero, if there can be no
empty cells

=SUMPRODUCT(--((A12/A4>55)+(A12/A4<45)>0))


I haven't really tested it except for the values in your example

 

[Guest]

Thank you all for your help!

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(--((A12/A4>55)+(A12/A4<45)>0),--(A12<>""))


to guard against empty cells who are counted as zero, if there can be no
empty cells

=SUMPRODUCT(--((A12/A4>55)+(A12/A4<45)>0))


I haven't really tested it except for the values in your example