Forum Discussion
Excel Formulas not Working
The formula (J679) I have in the first image should return a value of 1 if the conditions in B573 and B638 is greater than or = to 1, which should sum the H cells. With nothing in either cell (other than the formulas in the next 2 images), the content in the J679 cell is the sum of the H cells. This can't be correct since there is no 1 being displayed. If I remove the formulas and manually insert 0's or 1's into B573 and B638 the formula in J679 works perfectly. I typed this into AI and it gave me several solutions to try. The format for those cells is General. The font and background colors are different colors (so I would know if there was a 1 in the cell by accident). Any thoughts or suggestions?
2 Replies
Does it work if you add NUMBERVALUE to your formula?
=IF(AND(NUMBERVALUE(B573)>=1,NUMBERVALUE(B638)>=1),SUM(.......),"")
The problem is that "" is a text value and text is > numbers (think of sorting from low to high you want it to start with 0-9999... and then A-Z ...)
So as suggested by Oliver you can use NUMBERVALUE() or you can use N() or you can change the formulas in B573 and B638 to return an actual 0 instead of "" or maybe return "Y"/"N" and check for those values. If you want something more 'hidden" you can actually use " " vs " " and check >" " or check LEN()
