Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif two corresponding cells meet the criteria | Excel Worksheet Functions | |||
Count Cells that meet Criteria | Excel Worksheet Functions | |||
Sum Total # Of Cells That Meet Several Criteria | Excel Worksheet Functions | |||
Adding cells that meet certain criteria | Excel Programming | |||
Adding the sum of figures in one column if they meet 2 criteria | Excel Worksheet Functions |