Forum Discussion
Solved
Multiple if functions to simplify
I have some quantities in each column from B to H, if the value is greater than 6 I would like to divide with 6 and sum the value in all columns to J. For that I had used this formula. Is there a po...
Alternatively, with Excel 2021 or 365
in J7:
=SUM(ROUNDDOWN(IF(B7:H7 > 6, B7:H7/6, 0), 0))
Rudrabhadra wrote: ``=(IF(B7>6,ROUNDDOWN(B7/6,0),0))+....``
Ostensibly, simply normally-enter (just press Enter as usual) the following formula:
=SUMPRODUCT(--(B7:H7>6), ROUNDDOWN(B7:H7/6, 0))
The double negation ("--") converts TRUE and FALSE to 1 and 0, as SUMPRODUCT requires.
But if B7=6, do you truly want zero instead of 1?!
If you actually want 1 and each of B7:H7 is never negative, the formula can be simply:
=SUMPRODUCT(ROUNDDOWN(B7:H7/6, 0))