is < or together in same formula
 

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

is < or together in same formula
 

try
=sumproduct((b17:b264)*(b17:b26<4.4))
--
Don Guillett
SalesAid Software

"sgreene" wrote in message
...
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

is < or together in same formula
 

=SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4))
--
Allllen


"sgreene" wrote:

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

is < or together in same formula
 

=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)

--

HTH

Bob Phillips

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

"sgreene" wrote in message
...
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

is < or together in same formula
 

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


"Allllen" wrote:

=SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4))
--
Allllen


"sgreene" wrote:

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

is < or together in same formula
 

All formulas work on your original request

--
Don Guillett
SalesAid Software

"sgreene" wrote in message
...
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


"Allllen" wrote:

=SUMPRODUCT(--(b17:b264.0),--(b17:b26<4.4))
--
Allllen


"sgreene" wrote:

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

is < or together in same formula
 

Bob, thank you, but couldn't get it to work at all.

"Bob Phillips" wrote:

=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)

--

HTH

Bob Phillips

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

"sgreene" wrote in message
...
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

is < or together in same formula
 

You need to clearly restate your request along with examples.

--
Don Guillett
SalesAid Software

"sgreene" wrote in message
...
Bob, thank you, but couldn't get it to work at all.

"Bob Phillips" wrote:

=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)

--

HTH

Bob Phillips

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

"sgreene" wrote in message
...
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

is < or together in same formula
 

sgreene wrote:
"Bob Phillips" wrote:
=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)

Bob, thank you, but couldn't get it to work at all.

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.

is < or together in same formula
 

sgreene wrote:
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.

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.

is < or together in same formula
 

Hi Joe,
Tried your formula, but got a negative result in some of the columns

" wrote:

sgreene wrote:
"Bob Phillips" wrote:
=COUNTIF(B17:B264)-COUNTIF(B17:B26=4.4)

Bob, thank you, but couldn't get it to work at all.

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.

is < or together in same formula
 

sgreene wrote:
" wrote:
Bob's formula has some serious syntax errors. Try:
=COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4")

Tried your formula, but got a negative result in some of the columns

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.

is < or together in same formula
 

Technical Errata....

I wrote:
=COUNTIF(B17:B26, "4") - COUNTIF(B17:B26, "=4.4")
[....]
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.

I should have said: the only way ... is ... to have __more__ numbers
=4.4 than numbers 4. But that is just a nitpick. It is the same mathematical impossibility. The point is: every number =4.4 is also 4. The count of numbers 4 is always greater than or equal to the count of numbers =4.4.