How can I add up multiple IF results.

C

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

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

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))
 
A

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
 
P

Pecoflyer

crabflinger;298547 said:
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
 

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

Top