How can I add up multiple IF results.
 

Ok Gang,

I found a solution to an earlier problem, which leads me to a new one.

Here goes,.......

Ive got my sheet set up so that if a number is entered, it will indicate if
the data is too high or too low in a given range. For example, say that I
have a range of 0.5 to 2.5, and I enter anything either above or below those
limits, the result will be a "1". Anything that falls between 0.5 and 2.5
will have no result.

The problem that I have is that I cant figure out how to add up the "1's"

I want to add up the "1's" in cells A5:K5 with the answer in L5.

Can anyone please help?

How can I add up multiple IF results.
 

Try this:

=SUM(A5:K5)

If you tried that and got a result of 0 change your IF formulas to return 1
and not "1". Do not use quotes around numbers in formulas. Only quote
"text".

Or, you can get the count of cells that meet those criteria without the use
of a helper column and If formulas.

Something like this:

=SUMPRODUCT(--(A4:K4<""),--((A4:K4<0.5)+(A4:K42.5)))

--
Biff
Microsoft Excel MVP


"crabflinger" wrote in message
...
Ok Gang,

I found a solution to an earlier problem, which leads me to a new one.

Here goes,.......

Ive got my sheet set up so that if a number is entered, it will indicate
if
the data is too high or too low in a given range. For example, say that I
have a range of 0.5 to 2.5, and I enter anything either above or below
those
limits, the result will be a "1". Anything that falls between 0.5 and 2.5
will have no result.

The problem that I have is that I cant figure out how to add up the "1's"

I want to add up the "1's" in cells A5:K5 with the answer in L5.

Can anyone please help?

How can I add up multiple IF results.
 

Something like this:
=SUMPRODUCT(--(A4:K4<""),--((A4:K4<0.5)+(A4:K42.5)))

In this case the double unary is redundant on the 2nd array.

=SUMPRODUCT(--(A4:K4<""),(A4:K4<0.5)+(A4:K42.5))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=SUM(A5:K5)

If you tried that and got a result of 0 change your IF formulas to return
1 and not "1". Do not use quotes around numbers in formulas. Only quote
"text".

Or, you can get the count of cells that meet those criteria without the
use of a helper column and If formulas.

Something like this:

=SUMPRODUCT(--(A4:K4<""),--((A4:K4<0.5)+(A4:K42.5)))

--
Biff
Microsoft Excel MVP


"crabflinger" wrote in message
...
Ok Gang,

I found a solution to an earlier problem, which leads me to a new one.

Here goes,.......

Ive got my sheet set up so that if a number is entered, it will indicate
if
the data is too high or too low in a given range. For example, say that
I
have a range of 0.5 to 2.5, and I enter anything either above or below
those
limits, the result will be a "1". Anything that falls between 0.5 and
2.5
will have no result.

The problem that I have is that I cant figure out how to add up the "1's"

I want to add up the "1's" in cells A5:K5 with the answer in L5.

Can anyone please help?

How can I add up multiple IF results.
 

Hi

Have you 2 different questions here or what?

Ive got my sheet set up so that if a number is entered, it will indicate
if
the data is too high or too low in a given range. For example, say that I
have a range of 0.5 to 2.5, and I enter anything either above or below
those
limits, the result will be a "1". Anything that falls between 0.5 and 2.5
will have no result.

When you enter your value into A1, then in B1 you may have a formula
=AND(A1=0.5,A1<=2.5)
or
=--AND(A1=0.5,A1<=2.5)
or
=IF(AND(A1=0.5,A1<=2.5),1,"")


The problem that I have is that I cant figure out how to add up the "1's"

I want to add up the "1's" in cells A5:K5 with the answer in L5.

And the answer will be?
When you want to count only 1's, then
=SUMPRODUCT(--(A5:K5=1),A5:K5)


Arvi Laanemets

How can I add up multiple IF results.
 

crabflinger;298547 Wrote:
Ok Gang,

I found a solution to an earlier problem, which leads me to a new one.

Here goes,.......

Ive got my sheet set up so that if a number is entered, it will
indicate if
the data is too high or too low in a given range. For example, say
that I
have a range of 0.5 to 2.5, and I enter anything either above or below
those
limits, the result will be a "1". Anything that falls between 0.5 and
2.5
will have no result.

The problem that I have is that I cant figure out how to add up the
"1's"

I want to add up the "1's" in cells A5:K5 with the answer in L5.

Can anyone please help?

Here's a great link for the SUMPRODUCT function
http://www.xldynamic.com/source/xld....T.html#classic


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=83481