| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sum only positive values in a column?
Hi
given a column say 10 -2 5.53 5.2 -27 How to sum ignoring the -2 -27 etc (and then how to sum only the negatives) -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
zzapper
=SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "zzapper" wrote in message oups.com... Hi given a column say 10 -2 5.53 5.2 -27 How to sum ignoring the -2 -27 etc (and then how to sum only the negatives) -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
Nick Hodge wrote: zzapper =SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 -- HTH Nick Hodge Microsoft MVP - Excel Nick Great just what I wanted. Now how can I extend it such it SUMS only if adjacent Cell contains "eBay Payment" -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
=sumproduct((a1:a1000)*(b2:b100="Ebay")*a1:a100)
-- Don Guillett SalesAid Software "zzapper" wrote in message ups.com... Nick Hodge wrote: zzapper =SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 -- HTH Nick Hodge Microsoft MVP - Excel Nick Great just what I wanted. Now how can I extend it such it SUMS only if adjacent Cell contains "eBay Payment" -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
zzapper
That wasn't what you asked ;-) =SUMPRODUCT(($A$1:$A$1000=0)*($B$1:$B$1000="eBay Payment")*($A$1:$A$1000)) Should work Remember the eBay Payment must be exactly the same in each cell and this now looks for positives and zero, reverse the < for negatives -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "zzapper" wrote in message ups.com... Nick Hodge wrote: zzapper =SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 -- HTH Nick Hodge Microsoft MVP - Excel Nick Great just what I wanted. Now how can I extend it such it SUMS only if adjacent Cell contains "eBay Payment" -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
You've already received formula solutions to your problem, but another thing
you might be interested in trying is to do Data Filter Autofilter then choose "ebay payment" on that column and Custom greater than zero on that column.........then you will have those filtered items right there in front of you for inspection......then the SUBTOTAL formula will give you the sum, or average, or count of the values. Vaya con Dios, Chuck, CABGx3 "zzapper" wrote: Nick Hodge wrote: zzapper =SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 -- HTH Nick Hodge Microsoft MVP - Excel Nick Great just what I wanted. Now how can I extend it such it SUMS only if adjacent Cell contains "eBay Payment" -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
I hope that the OP doesn't lose too much sleep trying to decide whether or
not to add the zeroes, Nick. :-) -- David Biddulph "Nick Hodge" wrote in message ... zzapper =SUMIF($A$1:$A$1000,"0") or <0 for negatives. (Be aware tis will exclude zero so you may want to use =0 "zzapper" wrote in message oups.com... Hi given a column say 10 -2 5.53 5.2 -27 How to sum ignoring the -2 -27 etc (and then how to sum only the negatives) -- zzapper Best of VimTips http://www.vim.org/tips/tip.php?tip_id=305 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to sum only positive values in a column?
David Biddulph wrote: I hope that the OP doesn't lose too much sleep trying to decide whether or not to add the zeroes, Nick. :-) A multiple thank you for these various ways of solving my problem. Good Newsgroup! Haven't really played with a spreadsheet since Borland's Quattro! zzapper |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup function/sum function | Excel Discussion (Misc queries) | |||
| To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Lookup values in one column to return another | Excel Worksheet Functions | |||
Hybrid Mode
