Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum value Upper 0
I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz |
#2
|
|||
|
|||
If you want separate totals for each column:
=SUMIF(A2:A5,"0") =SUMIF(B2:B5,"0") =SUMIF(A2:A5,"<0") =SUMIF(B2:B5,"<0") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "ldiaz" wrote in message ... I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz |
#3
|
|||
|
|||
a simple sumif should do it
=SUMIF(H2:H22,"0") or =SUMPRODUCT((H2:H220)*H2:H22) -- Don Guillett SalesAid Software "ldiaz" wrote in message ... I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz |
#4
|
|||
|
|||
Take a look at =sumif() in excel's help:
=sumif(a2:a99,"0") and =sumif(a2:a99,"<0") You could even put a value in another cell: =sumif(a2:a99,"<"&b1) to add up all the values less than the value in B1 ldiaz wrote: I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz -- Dave Peterson |
#5
|
|||
|
|||
One way
=SUMPRODUCT(--(A1:A40),A1:A4) for total positive values - answer is 35 not 35.5 =SUMPRODUCT(--(A1:A4<0),A1:A4) for total negative values - answer -34.0 Change ranges for price and alter range size to suit. Regards Roger Govier ldiaz wrote: I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz |
#6
|
|||
|
|||
To all answered my question I want to thanks and say: Thank you so much...
Ldiaz "Dave Peterson" wrote: Take a look at =sumif() in excel's help: =sumif(a2:a99,"0") and =sumif(a2:a99,"<0") You could even put a value in another cell: =sumif(a2:a99,"<"&b1) to add up all the values less than the value in B1 ldiaz wrote: I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz -- Dave Peterson |
#7
|
|||
|
|||
Hi,
Use the formulas, =SUMIF(A2:A100,"0") for summing positive quantities, and =SUMIF(A2:A100,"<0") for summing negative quantities, where A2:A100 is the range of cells considered. Modify the range appropriately. Regards, B. R. Ramachandran "ldiaz" wrote: I want to sum datas of a column with these Datas. Usage Total Price 33.5 $65.5 -32.5 $-50.5 -1.5 $-5 1.5 $5 But I want to Sum only values upper 0. it should be: Usage Total: 35.5 & $70.5 also I want the viceversa. please help ldiaz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
Convert lower case charecters to upper case | Excel Discussion (Misc queries) | |||
List vector using upper and lower limits | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) | |||
How to change location A1 cell from upper right to upper left? | Excel Discussion (Misc queries) |