=if(iserror

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
624
Office Version
  1. 365
Platform
  1. Windows
hello

can i change this code so that if there is an error it changes it to somthing like this code
=IF(ISERROR($G$17/$H$17),"- - -",$G$17/$H$17)

thanks jason

Code:
=SUMPRODUCT(--($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475)
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
i cant see how you get an error anyway unless there is an error in one of the ranges
and fyi the -- is unnecessary if you are using *
=SUMPRODUCT(($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475)
or use
=SUMPRODUCT(--($E$31:$E$475="MABF"),--($O$31:$O$475="RED"),$H$31:$H$475)
 

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
624
Office Version
  1. 365
Platform
  1. Windows
Hello martindwilson

Thanks for your reply

the reason i get errors #N/A is some time i have empty rows and not always will my query go down to 475

thanks jason
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
The basic syntax of If(Iserror is

=IF(ISERROR(calc),"do this instead of error",calc)

replace calc with the calculation you want to test for an error.
replace "do this instead of error" with the value you want returned instead of the error.

The calculation you want to test for an error is
SUMPRODUCT(--($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475)

So it becomes
=IF(ISERROR(SUMPRODUCT(--($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475)),"do this instead of error",SUMPRODUCT(--($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475))

If you have XL2007+ it's muich easier with IFERROR
=IFERROR(calc,"do this instead of error")
=IFERROR(SUMPRODUCT(--($E$31:$E$475="MABF")*($O$31:$O$475="RED"),$H$31:$H$475),"do this instead of error")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,128,025
Messages
5,628,196
Members
416,299
Latest member
Kxs00301

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top