SUMPRODUCT with criteria ignoring errors in rage

Are the #N/A records necessary?

Resolving the errors is far easier and more efficient than trying to work around them.

Try this:

=SUM(IF(LEFT($A$2:$A$47,LEN(G2))=G2&"",IF(ISNUMBER($C$2:$E$47),$C$2:$E$47)))

Enter with Ctrl+Shift+Enter.

Originally Posted by jason.b75

Are the #N/A records necessary?

Resolving the errors is far easier and more efficient than trying to work around them.

I guess you are right however there always will be situation (at least in my case) that error pops up so I was trying to find a solution to ignore errors

Try

=SUMPRODUCT(SUMIFS($B$2:$B$47,$A$2:$A$47,$G$2:$G$5&"*",$B$2:$B$47,"<9.99999999999999E+307"))

Originally Posted by Phuocam

Try this:

=SUM(IF(LEFT($A$2:$A$47,LEN(G2))=G2&"",IF(ISNUMBER($C$2:$E$47),$C$2:$E$47)))

Enter with Ctrl+Shift+Enter.

Hi, amazing ... works perfectly

Originally Posted by Jonmo1

Try

=SUMPRODUCT(SUMIFS($B$2:$B$47,$A$2:$A$47,$G$2:$G$5&"*",$B$2:$B$47,"<9.99999999999999E+307"))

Hi,
trying to understand this formula ....

is there any was to use sumproduct with multiple criteria and at the same time ignore errors?

Originally Posted by adsako

Hi, amazing ... works perfectly

It seems I was to quick with my enthusiasm
formula returns 170,008.4445 for 737 however it should be 930,016.75

is there any was to use sumproduct with multiple criteria and at the same time ignore errors?

Not that I'm aware of.

the <9.99999999999999E+307 is saying only count numbers that are less than 9.99999999999999E+307 (the biggest number allowed in a cell, referred to as BIGNUM)
This is what allows the sumifs function to ignore the errors because it's only looking for NUMBERS that are less than Bignum. Errors are NOT numbers.

Then adding the sumproduct allows it to do multiple criteria (G2:G5) as an OR type of function.
A simplified example
=SUMPRODUCT(SUMIF(A1:A100,B1:B3,B1:B100))
This would be the same as doing
SUMIF(A1:A100,B1,B1:B100)+SUMIF(A1:A100,B2,B1:B100)+SUMIF(A1:A100,B3,B1:B100))

Originally Posted by Jonmo1

Not that I'm aware of.

the <9.99999999999999E+307 is saying only count numbers that are less than 9.99999999999999E+307 (the biggest number allowed in a cell, referred to as BIGNUM)
This is what allows the sumifs function to ignore the errors because it's only looking for NUMBERS that are less than Bignum. Errors are NOT numbers.

Then adding the sumproduct allows it to do multiple criteria (G2:G5) as an OR type of function.
A simplified example
=SUMPRODUCT(SUMIF(A1:A100,B1:B3,B1:B100))
This would be the same as doing
SUMIF(A1:A100,B1,B1:B100)+SUMIF(A1:A100,B2,B1:B100)+SUMIF(A1:A100,B3,B1:B100))

Hi,
apologies I was offline
Thank you for support

KR