Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing minus numbers from a sumif
I am using the following countif formula in my spreadsheet
=SUMIF($B$3:$B$158,B167,$R$3:$R$158)/COUNTIF($B$3:$B$158,B167) However I would like my sumif to ignore minus values (or replace them with a 0), have you any ideas how this could be done? Thanks Nick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing minus numbers from a sumif
This conforms with your formula
=SUM(IF(($B$3:$B$158=B16)*($R$3:$R$158=0),$R$3:$R $158))/COUNTIF($B$3:$B$158 ,B16) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter, but are you not just trying to average it, in which case you should use =AVERAGE(IF(($B$3:$B$158=B16)*($R$3:$R$158=0),$R$ 3:$R$158)) also an array formula, otherwise you sum the = 0 items, but divide by a count of all of them. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "nir020" wrote in message ... I am using the following countif formula in my spreadsheet =SUMIF($B$3:$B$158,B167,$R$3:$R$158)/COUNTIF($B$3:$B$158,B167) However I would like my sumif to ignore minus values (or replace them with a 0), have you any ideas how this could be done? Thanks Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotaling minus negative numbers and posative numbers | Excel Worksheet Functions | |||
Removing beginning minus sign from telephone numbers | Excel Discussion (Misc queries) | |||
col of minus numbers changed to plus numbers | Excel Worksheet Functions | |||
How do I change minus numbers to + numbers in a col in Excel? | Charts and Charting in Excel | |||
Sum of a row minus two smallest numbers | Excel Worksheet Functions |