Leaving cell blank when sumproduct result is 0

I need help the following formula.

=IF(SUMPRODUCT((Calculation!$C$4:$C$435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D$3:D$435<>""))=0,"",SUMPRODUCT((Calculation!C3:C435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D3:D435<>""),(Calculation!D3:D435)))

I am getting the result as #N/A instead of blank cell.

I am trying to get cell as blank when the result of the sumproduct is 0, I do not have the option of using another columns as I have only given columns to work with and this is an ongoing file which has data of several months.

Any help would be appreciated.

Thanks
Ankur

I think the reason you are getting the #N/A error is because your ranges are not of equal size - the first one is for C4:C435, but all the others are for rows 3 to 435. You might get other errors if any of those cells contain errors, but if they are clear of errors then your formula should work with that correction.

Hope this helps.

Pete

=IF(SUMPRODUCT((Calculation!$C$4:$C$435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D$3:D$435<>""))=0,"",SUMPRODUCT((Calculation!C3:C435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D3:D435<>""),(Calculation!D3:D435)))

Make sure all your ranges are the same size.

Thanks Pete & WHER for the quick reverts, I have made all ranges of the same size however I still get result as 0 and not blank cell.

Hi Chris,

I tried iferror(your formula),"") and got a new error #NAME?

Upon your suggestion I did check the cell values of D3:D435, they are set at 0 decimal values. In case there is a value such as 0.49, 0.51 the result I am getting is 1 which is perfect however for values 0.00, 0.00 I am getting the result as 0.00 and I am looking for the cell as blank instead of a 0.

Let me know if this is doable

Well, you could try rounding the first part of your formula, like this:

=IF(ROUND(SUMPRODUCT((Calculation!$C$3:$C$435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D$3:D$435<>"")),0)=0,"",SUMPRODUCT((Calculation!C3:C435=MTD!C29)*(Calculation!A3:A435=MTD!B29)*(Calculation!D3:D435<>""),(Calculation!D3:D435)))

It's up to you if you want to also round the second part in the same way - it depends what you do with the returned values, and whether you want to treat them as integers or not in other calculations.

Hope this helps.

Pete

Yeah, well done dredwolf - you've nailed it. The first part of the OP's formula is just counting the columns that match the criteria (and returns a blank only if the count is zero), but then the second part sums the values from column D where there are matches, and from what he has said, these could be small values that look like 0 (or might even be zero) when displayed as integers.

Pete