Forum Discussion
Turney_Ryan
Sep 06, 2023Copper Contributor
If(And) Function not returning expected value
I'm trying to work on a function with multiple If(and) formulas, and I can't work out why my sum total isn't within the range of the formula.
See below.
CELL | # Total | Formula | Result | Problem |
A1 | 3 | =IF(AND((A1)>0,(A1)<350), "A","B") | A | Accurate (within 0-350 range) |
A2 | 88 | =IF(AND((A1:A2)>0,(A1:A2)<350), "A","B") | A | Accurate (within 0-350 range) |
A3 | 51 | =IF(AND((A1:A3)>0,(A1:A3)<350), "A","B") | A | Accurate (within 0-350 range) |
A4 | 80 | =IF(AND((A1:A4)>0,(A1:A4)<350), "A","B") | A | Accurate (within 0-350 range) |
A5 | 108 | =IF(AND((A1:A5)>0,(A1:A5)<350), "A","B") | A | Accurate (within 0-350 range) |
A6 | 116 | =IF(AND((A1:A6)>0,(A1:A6)<350), "A","B") | A | Should be B result as the total of A1:A6 is 446, and outside of 0-350 range. |
I also wanted to add additional formulas, such as the below
A6 | 116 | =IF(AND((A1:A6)>0,(A1:A6)<350), "A","")&IF(AND((A1:A6)>0,(A1:A6)<700), "A","B") | AA | Should be A only as result is within the 350-700 range, and based on the first formula is is not within 0-350 range, should return nothing ("") as a true value. |
Can anyone assist?
- OliverScheurichGold Contributor
=IF(AND(SUM($A$1:A1)>0,SUM($A$1:A1)<350), "A","B")
For the first part you can enter this formula in cell B1 and fill it down to B6. It returns the intended results in my sheet.
=IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<350), "A","")&IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<700), "A","B")
For the additional formula you can try this one in cell B6. The result is "A" in my sheet because the SUM(A1:A6) is 446.