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

[drake171]

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!

 

[Scott T]

What version of Excel do you have? IFS is only available in Excel 2016.

 

[dreid1011]

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"))

 

[wideboydixon]

What about when SUM(BO5:BO7)>$BV$6 ?? There's no condition for that which could cause a #N/A issue.

WBD

 

[wideboydixon]

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

WBD

 

[drake171]

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