Multiple if functions to simplify

[Rudrabhadra]

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 possibility to make this simpler …

I had used this one as mentioned below. Looking for a simpler one.

=(IF(B7>6,ROUNDDOWN(B7/6,0),0))+(IF(C7>6,ROUNDDOWN(C7/6,0),0))+(IF(D7>6,ROUNDDOWN(D7/6,0),0))+(IF(E7>6,ROUNDDOWN(E7/6,0),0))+(IF(F7>6,ROUNDDOWN(F7/6,0),0))+(IF(G7>6,ROUNDDOWN(G7/6,0),0))+(IF(H7>6,ROUNDDOWN(H7/6,0),0))

I tried with sumif as well ... but something is mistake the formulae is mentioned below

=SUMIF(B7:H7,B7:H7>20,B7:H7)

Thanks in advance...

[#2]

@Rudrabhadra 

=SUMPRODUCT(IF(B7:H7>6,ROUNDDOWN(B7:H7/6,0),0))

You can try this formula for the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

@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))

[#4 · Accepted solution]

@Rudrabhadra 

Alternatively, with Excel 2021 or 365

in J7:

=SUM(ROUNDDOWN(IF(B7:H7 > 6, B7:H7/6, 0), 0))