=if(iserror

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
703
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)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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
703
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,531
Messages
5,854,284
Members
431,636
Latest member
shabbas313

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