IFERROR formula

L

Legacy 452653

Guest
Hi,

Im working on a warehouse database with orders and quotes I have this IF formula =IF($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote)) But its giving me a $REF in all cells.

This is because the cells reference too, Data is not in the cell., Im trying to do a IFERROR so that when there is no data it should represent "0"

I have tried =IFERROR($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote),"") & =IFERROR($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote),"0")) and still does not work

Can you help me on this iferror formula

Thanks

Steve
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
195
Office Version
  1. 2019
Platform
  1. Windows
maybe
=IFERROR(IF($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote)), 0)
if a formula is giving you an error and you want to IFERROR it, use the formula as it is. you shouldn't have removed the IF in IF($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote))
=IFERROR(your formula that's giving the error, 0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,475
Office Version
  1. 365
Platform
  1. Windows
This is because the cells reference too, Data is not in the cell.,
I don't understand that, but it sounds like one or both of your named ranges does not exist.
If you go to the Formulas ribbon tab and then Name Manager, what do you see in relation to the names CheckOrderCommitted and CommittedQuote

In the interim, unless B2 contains a formula that could return an error, you should only need the IFERROR on the SUMIF part of your formula.
And don't put quotes around the zero.

=IF($B2="","",IFERROR(SUMIF(CheckOrderCommitted,B2, CommittedQuote),0))
 
L

Legacy 452653

Guest
Bang on the money.

Unbelievable thanks for this. The thing works!

Legend!
 
L

Legacy 452653

Guest

ADVERTISEMENT

From the previous reply
=IFERROR(IF($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote)), 0)

Works to a T !

But thanks anyhow

Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,475
Office Version
  1. 365
Platform
  1. Windows
Glad you got a successful outcome. Thanks for letting us know. :)
 
L

Legacy 452653

Guest
Interestingly, that formula work as well so how about that!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,458
Messages
5,636,390
Members
416,917
Latest member
koto1

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