Excel - If any of 6 cells are > 0 divide by that number of cells

Question

What I'm trying to do is the following:

Once 'Cell B19:B20' and 'Row 21' have a value, 'Row 22' divides 'Cell B19:B20' between the amount of columns that have a cost value ('Row 21').

For example:

if only D21 has a value the D22=B19:B20

if D21 AND F21 have values then D22=(B19:B20/2)

if D21 AND F21 AND H21 have values then D22=(B19:B20/3)

Basically calculate regardless of the order of columns but only accounting for the number of columns that have a value.

These are the formulas I have so far but they only calculate if the columns are in order (A, B, C, D) not random (A, C, H):

=IF((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/6),IF((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0),(B19/5),IF((D21>0)*AND(E21>0)*AND(F21>0)*AND(G21>0),(B19/4),IF((D21>0)*AND(E21>0)*AND(F21>0),(B19/3),IF((D21>0)*AND(E21>0),(B19/2),IF((D21>0),B19,0))))))

=IF((E21=0),0,IF((D22>0),D22,IF((E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/5),IF((E21>0)*AND(F21>0)*AND(G21>0)*AND(H21>0),(B19/4),IF((E21>0)*AND(F21>0)*AND(G21>0),(B19/3),IF((E21>0)*AND(F21>0),(B19/2),IF((E21>0),B19,0)))))))

=IF((F21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F21>0)*AND(G21>0)*AND(H21>0)*AND(I21>0),(B19/4),IF((F21>0)*AND(G21>0)*AND(H21>0),(B19/3),IF((F21>0)*AND(G21>0),(B19/2),IF((F21>0),B19,0)))))))

=IF((G21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G21>0)*AND(H21>0)*AND(I21>0),(B19/3),IF((G21>0)*AND(H21>0),(B19/2),IF((G21>0),B19,0)))))))

=IF((H21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G22>0),G22,IF((H21>0)*AND(I21>0),(B19/2),IF((H21>0),B19,0)))))))

=IF((I21=0),0,IF((D22>0),D22,IF((E22>0),E22,IF((F22>0),F22,IF((G22>0),G22,IF((H22>0),H22,IF((I21>0),B19,0)))))))

Answer 1

This array (CSE) formula can solve the issue:

How it works:

• Two values in Cell A103 & in B103 to dived with count of cells, (A101:E101) have value other than ZERO.

• Array formula in Cell C103:

  {=IF(COUNTIF(A101:E101,">0")=0,0,(A103:B103)/COUNTIF(A101:E101,">0"))}
  

N.B:

• Finish formula with Ctrl+Shift+Enter.

• COUNTIF formula in cell F101, justifies the Count.

• Later on you may hide cell F101.

Adjust cell references in the Formula as needed.

Answer 2

You're using AND() in a weird way. Separate all the conditions that you want to combine with AND() with commas inside the brackets of the AND(). You are using the *, which already does what AND() does, too, so you're really doubling up the concept.

With AND() applied properly, your formula can start like this:

=IF(AND(D21>0,E21>0,F21>0,G21>0,H21>0,I21>0),B19/6),
    IF(AND(D21>0,E21>0,F21>0,G21>0,H21>0),(B19/5), [...]

and so on

A much shorter way to achieve the same result, though, is to look at the sum of the cells.

=IF(Sum(D21:I21)>0,B19/6,
   IF(Sum(D21:H21)>0,B19/5,
      IF(sum(D21:G21)>0,B19/4,
        IF(sum(D21:F21)>0,B19/3,
          IF(sum(D21:E21)>0,B19/2,
             IF(D21>0,B19,0)
          )
         )
       )
    )
  )

Strictly speaking, you don't even need the >0 in the conditions, since a 0 will evaluate to False and any number greater than zero will evaluate to True.

=IF(Sum(D21:I21),B19/6,
   IF(Sum(D21:H21),B19/5,
      IF(sum(D21:G21),B19/4,
         IF(sum(D21:F21),B19/3,
            IF(sum(D21:E21),B19/2,
               IF(D21,B19,0))))))

Go easy on the brackets. Sometimes, an extra pair of brackets can help where they are not really required, but don't apply them everywhere.