Forum Discussion

Turney_Ryan's avatar
Turney_Ryan
Copper Contributor
Sep 06, 2023

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# 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?

  • Turney_Ryan 

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

Resources