is < or > together in same formula

S

sgreene

I need to do a formula and have had no luck.
Need to do a countif for b17:b26 but I also need to in that list if there
are numbers <4.4 and >4.0
Any formula I have tried does not work.
Thanks
 
B

Bob Phillips

=COUNTIF(B17:B26>4)-COUNTIF(B17:B26>=4.4)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
S

sgreene

Allllen,
Tried your formula...4 rows of numbers on top are what they should be...4
numbers on the bottom are what they come out with your formula. Some are
correct and some are not??? Can you suggest anything. I truly appreciate
your help.

4 5 0 0 10 3
6 3 3 0 0 5
0 2 4 2 0 2
0 0 3 8 0 0


4 5 0 0 10 3
4 3 1 0 0 1
0 2 4 2 0 2
0 0 3 8 0 0
 
J

joeu2004

sgreene said:
Bob, thank you, but couldn't get it to work at all.
Click to expand...

You are correct. Bob's formula has some serious syntax errors. Try:

=COUNTIF(B17:B26, ">4") - COUNTIF(B17:B26, ">=4.4")

However, I believe that should have the same results as Don's
SUMPRODUCT formulation. You already said Don's formula did not always
produce the results you expect.
 
J

joeu2004

sgreene said:
Tried your formula...4 rows of numbers on top are what they should be...4
numbers on the bottom are what they come out with your formula. Some are
correct and some are not??? Can you suggest anything.
Click to expand...

It would be helpful if you posted the contents of the cells of one or
two ranges that did not sum as you expected. Two possible explanations
come to mind:

1. You misstated your conditons, and you are really interested in >=4
and/or <=4.4. When you summed by hand, you subconsciously included
those numbers that are equal to one or both ends of the range. But be
wary of testing for equality in Excel. It is subject to
misinterpretation due to the difference between displayed and actual
values (e.g., 3.99 might appear as "4.0").

2. Some of the cells that you intend to contain numbers are formatted
as Text.
 
J

joeu2004

sgreene said:
Tried your formula, but got a negative result in some of the columns
Click to expand...

First, it is Bob's formula, not mine. I merely corrected Bob's typos.

Second, the fact that you got some negative results suggests that there
is something very wrong with the data in your range(s). The only way
for that formula to have a negative result (if it is entered correctly)
is for the range to have some numbers >=4.4, but no numbers >4. I hope
you can see that that is a mathematical impossibility.

At this point, I think any further discussion is impossible without
your posting a concrete example, complete with formulas used, as Don
suggested. But please be sure to cut-and-paste. The root cause of
your problems might be some typos.
 
J

joeu2004

Technical Errata....
[....]
The only way
for that formula to have a negative result (if it is entered correctly)
is for the range to have some numbers >=4.4, but no numbers >4. I hope
you can see that that is a mathematical impossibility.
Click to expand...

I should have said: the only way ... is ... to have __more__ numbers
 
Top