IF/THEN & a SUMIF & COUNTIF Formula

**dagindi** · 05-24-2010, 07:37 PM

I am using the following formula to total "withdrawals" (represented by negative numbers) from a column and then dividing it by the count and multiplying it to get the positive average:

Please Login or Register  to view this content.

Works great except when there is nothing in the column, then you get the good ole #/DIV0! error.

I played around with a few different IF/THEN statements (two samples below) and none of them are being accepted.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

What am I missing??

**shg** · 05-24-2010, 07:47 PM

Maybe =-SUMIF(G11:G250,"<0") / MAX(1, COUNTIF(G11:G250,"<0"))

**dagindi** · 05-25-2010, 09:48 AM

Shg,

Works perfectly! Who would of thought you can put a "-" before the SumIf to make the result positive.

I am dissecting the formula you provided and I understand most of it.

I cant figure out how the "Max(1,...)" affects the formula to return the result if >0 or the "0" if <0.

Can you give me a brief explanation on this?