Ignore error while doing Sumproduct

[neeraj2050]

Hi,

While doing sumproduct, result is #N/A as there are #N/A into data

Is there any way to eliminate that issue without finding the fix error cells

File is attached for reference

Regards
Neeraj Kumar Agarwal

 

[deciog]

try this way

=SUMPRODUCT(IFERROR(A4:A7,0),IFERROR(B4:B7,0))

Decio

 

[Khalid NGO]

Hi,

Or use this {array formula}

=SUM(IFERROR(A1:A7*B1:B7,0))

{array formula needs to be entered with Ctrl+Shift+Enter}

Regards,

 

[neeraj2050]

@deciog: It's not working

@Khalid NGO : Thanks! It worked

 

[Khalid NGO]

The formula posted by decoig is also an Array Formula, will work with Ctrl+Shift+Enter

 

[deciog]

Khalid NGO

I always forget because my version of Excel 365 no longer needs to use these keys

Thank you

Decio

 

[Khalid NGO]

Hi Decio,

"We are all very ignorant. What happens is that not all ignore the same things." AE

Regards,

 

[neeraj2050]

@Decio,Khalid: Thanks a lot!

 

[bosco_yip]

Khalid NGO

I always forget because my version of Excel 365 no longer needs to use these keys

Thank you

Decio

@deciog ,

Please check with your Office 365 with Dynamic array function version,

I think if =SUMPRODUCT(IFERROR(A4:A7,0),IFERROR(B4:B7,0)) need not array entry

then,

=SUM(IFERROR(A4:A7,0),IFERROR(B4:B7,0)) maybe also need not array entry.

But,

some Office 365 subscribers without Dynamin array function, the above function still need array entry.

Regards

 

[deciog]

bosco_yip

In my version 365, this function =SUM(IFERROR(A4:A7,0),IFERROR(B4:B7,0)) doesn't need the matrix input either

Hugs

Decio

 

[bosco_yip]

bosco_yip

In my version 365, this function =SUM(IFERROR(A4:A7,0),IFERROR(B4:B7,0)) doesn't need the matrix input either

Hugs

Decio

@deciog ,

Thank you for your reply

Bosco