| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selective addition based on criteria
I have a column of information that contains various numerical entries.
I want to sum the entries to get a total. The entries are both positive and negative numbers. If an entry contains the value -250, I want to sum only the negative values (including the -250 and any other possible -250 entries) omitting any positive entries. Otherwise it will sum both negative and positive values. Thank you for your assistance! :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selective addition based on criteria
Randy,
I have provided an example at http://www.HelpExcel.com/examples The name of the spreadsheet is sum.xls -- http://HelpExcel.com "Randy L" wrote: I have a column of information that contains various numerical entries. I want to sum the entries to get a total. The entries are both positive and negative numbers. If an entry contains the value -250, I want to sum only the negative values (including the -250 and any other possible -250 entries) omitting any positive entries. Otherwise it will sum both negative and positive values. Thank you for your assistance! :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selective addition based on criteria
Larry,
To get it into one formula, you can type the following: =IF(SUM(IF(A1:A4=-250,1,0))0,SUM(IF(A1:A4<0,A1:A4,0)),SUM(A1:A4)) Make the scope of the ranges appropriate and be sure to press control-shift-enter to let Excel know it is a formulaic array -- http://HelpExcel.com "Randy L" wrote: I have a column of information that contains various numerical entries. I want to sum the entries to get a total. The entries are both positive and negative numbers. If an entry contains the value -250, I want to sum only the negative values (including the -250 and any other possible -250 entries) omitting any positive entries. Otherwise it will sum both negative and positive values. Thank you for your assistance! :) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selective addition based on criteria
Thank you Galimi. This works great :)
"galimi" wrote: Larry, To get it into one formula, you can type the following: =IF(SUM(IF(A1:A4=-250,1,0))0,SUM(IF(A1:A4<0,A1:A4,0)),SUM(A1:A4)) Make the scope of the ranges appropriate and be sure to press control-shift-enter to let Excel know it is a formulaic array -- http://HelpExcel.com "Randy L" wrote: I have a column of information that contains various numerical entries. I want to sum the entries to get a total. The entries are both positive and negative numbers. If an entry contains the value -250, I want to sum only the negative values (including the -250 and any other possible -250 entries) omitting any positive entries. Otherwise it will sum both negative and positive values. Thank you for your assistance! :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting occurences based on two criteria | Excel Discussion (Misc queries) | |||
| Delete rows based on criteria | Excel Discussion (Misc queries) | |||
| how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
| caluculate an average based on 2 criteria | Excel Discussion (Misc queries) | |||
| SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
