Adding cells that meet certain criteria
 

I have a row of 24 numbers, A1:X1. I would like Y1 to SUM only those cells
if they have negative numbers in them and Z1 to SUM only those cells that
have positive numbers. Thanks!
--
Patti

Adding cells that meet certain criteria
 

In Y1 =SUMPRODUCT((A1:X1)*(A1:X1<0))
In Z1 =SUMPRODUCT((A1:X1)*(A1:X10))
--
HTH...

Jim Thomlinson


"PattiP" wrote:

I have a row of 24 numbers, A1:X1. I would like Y1 to SUM only those cells
if they have negative numbers in them and Z1 to SUM only those cells that
have positive numbers. Thanks!
--
Patti

Adding cells that meet certain criteria
 

P.S. sorry, I got the "positive/negative" mixed up, but I think you can
figure it out.

"PattiP" wrote:

I have a row of 24 numbers, A1:X1. I would like Y1 to SUM only those cells
if they have negative numbers in them and Z1 to SUM only those cells that
have positive numbers. Thanks!
--
Patti

Adding cells that meet certain criteria
 

Very interesting! I'm sold.

P.S. in all fairness to SUM(IF(... the IF() syntax (A1:An="?")*(B1:Bn="?")
works the same as in SUMPRODUCT, without having to use multiple IF()
statements as shown at that web site. But SUMPRODUCT seems to be a
"packaged" SUM(IF( function, much nicer and no Ctrl+Shift+Enter.

"Jim Thomlinson" wrote:

If you are interested here is a link to a site that gives a lot of great info
on the sumproduct formula...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This is a very useful reference that I highly recommend.
--
HTH...

Jim Thomlinson


"PattiP" wrote:

Thanks to you both !!!!
--
Patti


"Charlie" wrote:

in Y1
=SUM(IF(A1:X10,A1:X1))
in Z1
=SUM(IF(A1:X1<0,A1:X1))

NOTE: after entering the formulas you must hold down both the Shift and Ctrl
keys at the same time, then press Enter (Ctrl+Shift+Enter). You will see the
formula in curly braces if you do it correctly. This gives you an "array
formula" to calclulate on the whole range.

{=SUM(IF(A1:X10,A1:X1))}

"PattiP" wrote:

I have a row of 24 numbers, A1:X1. I would like Y1 to SUM only those cells
if they have negative numbers in them and Z1 to SUM only those cells that
have positive numbers. Thanks!
--
Patti

Adding cells that meet certain criteria
 

If you are interested here is a link to a site that gives a lot of great info
on the sumproduct formula...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This is a very useful reference that I highly recommend.
--
HTH...

Jim Thomlinson


"PattiP" wrote:

Thanks to you both !!!!
--
Patti


"Charlie" wrote:

in Y1
=SUM(IF(A1:X10,A1:X1))
in Z1
=SUM(IF(A1:X1<0,A1:X1))

NOTE: after entering the formulas you must hold down both the Shift and Ctrl
keys at the same time, then press Enter (Ctrl+Shift+Enter). You will see the
formula in curly braces if you do it correctly. This gives you an "array
formula" to calclulate on the whole range.

{=SUM(IF(A1:X10,A1:X1))}

"PattiP" wrote:

I have a row of 24 numbers, A1:X1. I would like Y1 to SUM only those cells
if they have negative numbers in them and Z1 to SUM only those cells that
have positive numbers. Thanks!
--
Patti