IF function issues - maybe I should be using different formula??

drake171

New Member
Joined
Apr 12, 2018
Messages
4
I created a hidden table to capture total counts from a data entry table containing 16 required columns/items and up to 10 non-required columns/items that may or may not be filled in. The data entry options are "Y", "N", or "N/A". I have the hidden table setup to obtain totals in the form of a numerator (Y+N/A) and validated denominator (Y+N/A+N) for each column, as well as an overall required and non-required column average so I can roll the various totals into other sheets for higher level views.

I am having a lot of trouble with the non-required portion of my hidden table. I thought I had a pretty solid formula, but it is returning #NAME ? rather than "ERR" which is what I set it up to return, and I haven't been able to figure out a better solution.

Here is my current formula::
=IFS(SUM(BO5:BO7)<$BV$6,"ERR",SUM(BO5:BO7)=$BV$6,SUM(BO5:BO7),SUM(BO5:BO7)=0,"")
  • The section in red is not working and is returning the #NAME ? error
  • The section in green works fine


  • Cell BV6 is what I am validating the denominator with, so if the numbers match I want it to return the validated number
  • If there is no data to add, then I want the total to remain blank (because this section isn't required)
  • If the denominator is greater than zero or less than BV6, I want it to return "ERR". Is there a better way to write a formula to match this statement? I am thinking that my third IF statement in the formula is screwing up the results of the first one because 0 is less than BV6, I just don't know how to fix it!

Any help is greatly appreciated!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
What version of Excel do you have? IFS is only available in Excel 2016.
 
Upvote 0
I've tested the formula (Excel 2016) and it seems to work ok? I am not receiving any NAME? errors, but I might suggest swapping your 1st and 3rd tests. As it is, it will always return "ERR" even with a 0 result. So, if you want 0 to return blank, then you should test that first.

With that in mind, and based on Scott's question, how about this?

=IF(SUM(BO5:BO7)=0,"",IF(SUM(BO5:BO7)=$BV$6,SUM(BO5:BO7),"ERR"))
 
Last edited:
Upvote 0
What about when SUM(BO5:BO7)>$BV$6 ?? There's no condition for that which could cause a #N/A issue.

WBD
 
Upvote 0
Code:
=IF(SUM(BO5:BO7)<$BV$6,"ERR",IF(SUM(BO5:BO7)=$BV$6,SUM(BO5:BO7),IF(SUM(BO5:BO7)=0,"","Unhandled case")))

WBD
 
Upvote 0
YAY!! Thank you, I feel so silly - this worked perfectly. Now I need to test this change with the rest of my table and see if I can get it to all work in unison. Thanks again :)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top