Countif (like no other)

H

hnyb1

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
 
D

Don Guillett

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

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(--((A1:D2/A4>55)+(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/A4>55)+(A1:D2/A4<45)>0))


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

hnyb1

Thank you all for your help!

Peo Sjoblom said:
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/A4>55)+(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/A4>55)+(A1:D2/A4<45)>0))


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif formula 2
COUNTIF, Sorting, Two Sheets 0
countif 5
New to Banter... 1
Countif/Vlookup 2
countif 3
COUNTIF Cells in Range? 4
Countif Conditions - Use of conditions that vary by cell value 1

Top