SUMIF Error

**lesoies** · 01-22-2013, 11:45 PM

Can anyone tell me why the following SUMIF formula doesn't work? I get a "the formula you typed contains an error" message when I ctrl+shift+enter:
=SUMIF(N(OFFSET(Stats!$B$4,D64:D81,))/MMULT(N(OFFSET(Stats!$B$4,D64:S81,)),TRANSPOSE(COLUMN(D64:S81)^0)),">0")/18

I find this odd because the same formula works with SUM:
=SUM(N(OFFSET(Stats!$B$4,D64:D81,))/MMULT(N(OFFSET(Stats!$B$4,D64:S81,)),TRANSPOSE(COLUMN(D64:S81)^0)))/18

Essentially, I am attempting to avoid DIV/0! errors using this method. I have seen other methods but it makes the code harder to follow and I'm trying to keep it as clean as possible. Can anyone help! I could use some wizardry!

**dilipandey** · 01-23-2013, 12:49 AM

Hi lesoies,

It appears that both the formulas are not same.. In SumIf you are considering ">0" but In SUM, there is no such consideration.

Regards,
DILIPandey
<click on below * if this helps>

**Teethless mama** · 01-23-2013, 12:50 AM

You can not manipulate with SUMIF(). Try SUMPRODUCT() instead

**lesoies** · 01-23-2013, 01:44 AM

SUMIF looks like it should work which is the frustrating part - do you know why it doesn't? i was hoping that i just had some kind of weird syntax error.

**Teethless mama** · 01-23-2013, 02:25 AM

Originally Posted by lesoies

SUMIF looks like it should work which is the frustrating part - do you know why it doesn't? i was hoping that i just had some kind of weird syntax error.

Nope! Like I said in post #3. SUMIF only work "straight comparison"

Example: SUMIF(A:A,"apple",B:B)