Removing minus numbers from a sumif

[nir020]

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

[Bob Phillips]

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" <nir020@discussions.microsoft.com> wrote in message
news:9896B3A7-A38C-46BF-B9A9-A60022B7573B@microsoft.com...
> 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
>