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
|