How can I add up multiple IF results.

[crabflinger]

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?

 

[T. Valko]

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:K4>2.5)))

 

[T. Valko]

Something like this:

=SUMPRODUCT(--(A4:K4<>""),--((A4:K4<0.5)+(A4:K4>2.5)))

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

=SUMPRODUCT(--(A4:K4<>""),(A4:K4<0.5)+(A4:K4>2.5))

 

[Arvi Laanemets]

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

 

[Pecoflyer]

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.SUMPRODUCT.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