# Sumproduct formula returns Zero instead of Error

#### amandeep08

##### Board Regular
I am trying to use sum product formula in Excel.

If the complete criteria is not met as per the formula, it should return error while it is showing as Zero.

Pl help

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
If the range you sum and the ranges you test do not contain any error value, the result is either a non-zero value or a zero. Why do you want otherwise?

generic answer: The SUMPRODUCT function will only result in an error if you applied it wrongly, for help on the reasons for that take a look at this: Sumproduct and its error messages

Most probable cause in your case: your formula is build up using criteria and one (or more) part(s) aren't met.
If you apllied the function correctly it will evaluate your criteria to 0 if their not (fully) met it.

If you want it to come up with an error on the evaluation of the criteria, combine it with using the IFERROR function.

Last edited:
I want the error because I have put the formula on the commercials and if the formula shows error, I came to know that the commercials needs to be revised.

I have cases where the commercial terms are 0% and when this formula give value as 0, it is difficult for us to check each & every line item

ok, try this:

Excel 2016 (Windows) 64 bit
ABCDE
1Case 1Apple7
2Case 2Pears1760
3Case 3Bananas179#N/A
4Case 4Apple135
5Case 5Pears53
6Case 6Bananas39
7Case 7Apple13
8Case 8Pears44
9Case 9Bananas48
Sheet1
Cell Formulas
RangeFormula
E2=SUMPRODUCT((B1:B9="Apples")*(VALUE(RIGHT(A1:A9,1))<=5)*(C1:C9))
E3{=IF((B1:B9="Apples")*(VALUE(RIGHT(A1:A9,1))<=5),SUMPRODUCT((B1:B9="Apples")*(VALUE(RIGHT(A1:A9,1))<=5)*(C1:C9)),#N/A)}
Press CTRL+SHIFT+ENTER to enter array formulas.

E2 contains your original situation in which the creiteria isn't fully met so SUMPRODUCT delivers 0
E3 has an error creation within.

Last edited:
This will not work for me because I already have multiple commands that if this then this else if this then this like that.

Pl help Other than if command

Last edited:
This will not work for me because I already have multiple commands that if this then this else if this then this like that.

Pl help Other than if command

Care to post the formula in question?

Hi,

I want if the data is not matching, then it will throw error instead of ZERO.

https://we.tl/n2neWpHsQu

You've mentioned:
I already have multiple commands that if this then this else if this then this

Care to post the formula in question?

Your formula doesn't show in the file. It's just your question repeated.

Hi,

The formula is like this:

=IF(H5574="Per Veh Slab",J5574*(SUMPRODUCT((\$E5574=NEFT!\$A\$2:\$A\$63655)*(\$O5574>=NEFT!\$M\$2:\$M\$63655)*(\$O5574<=NEFT!\$N\$2:\$N\$63655)*(\$K5574>=NEFT!\$I\$2:\$I\$63655)*(\$K5574<=NEFT!\$J\$2:\$J\$63655),NEFT!\$O\$2:\$O\$63655)),IF(Z5574="YTD Slab",(SUMPRODUCT(--(N5574>{0;5000000;10000000;30000000;50000000}),(N5574-{0;5000000;10000000;30000000;50000000}),{0.025;0.005;0.005;0.005;0.01}))-SUMPRODUCT(--(M5574>{0;5000000;10000000;30000000;50000000}),(M5574-{0;5000000;10000000;30000000;50000000}),{0.025;0.005;0.005;0.005;0.01}),(ROUND(L5574*Z5574,0))))

Will not be able to attach excel as the file is heavy and confidential.

Replies
4
Views
342
Replies
1
Views
74
Replies
7
Views
429
Replies
3
Views
408
Replies
5
Views
332

1,203,102
Messages
6,053,538
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back