If(And) Function not returning expected value

Copper Contributor

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# TotalFormulaResultProblem
A13=IF(AND((A1)>0,(A1)<350), "A","B")AAccurate (within 0-350 range)
A288=IF(AND((A1:A2)>0,(A1:A2)<350), "A","B")AAccurate (within 0-350 range)
A351=IF(AND((A1:A3)>0,(A1:A3)<350), "A","B")AAccurate (within 0-350 range)
A480=IF(AND((A1:A4)>0,(A1:A4)<350), "A","B")AAccurate (within 0-350 range)
A5108=IF(AND((A1:A5)>0,(A1:A5)<350), "A","B")AAccurate (within 0-350 range)
A6116=IF(AND((A1:A6)>0,(A1:A6)<350), "A","B")AShould 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


A6116=IF(AND((A1:A6)>0,(A1:A6)<350), "A","")&IF(AND((A1:A6)>0,(A1:A6)<700), "A","B")AAShould 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?

1 Reply


=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.