Sumproduct formula returns Zero instead of Error

[amandeep08]

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

 

[Aladin Akyurek]

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?

 

[jorismoerings]

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.

 

[amandeep08]

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

 

[jorismoerings]

ok, try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	Case 1	Apple	7
2	Case 2	Pears	176		0
3	Case 3	Bananas	179		#N/A
4	Case 4	Apple	135
5	Case 5	Pears	53
6	Case 6	Bananas	39
7	Case 7	Apple	13
8	Case 8	Pears	44
9	Case 9	Bananas	48
Sheet1

Cell Formulas
Range	Formula
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.

 

[amandeep08]

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

 

[Aladin Akyurek]

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?

 

[amandeep08]

Hi,

I have loaded the file in the below link.

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

https://we.tl/n2neWpHsQu

 

[jorismoerings]

You've mentioned:

I already have multiple commands that if this then this else if this then this

And Aladin asked:

Care to post the formula in question?

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

 

[amandeep08]

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.