IF clause for a range of cells

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

The following formula works well for me but is there a way to make it shorter
by defining a range of cells, rather than individual cells, in the IF part?

=SUM(G8:AJ8)+IF(G8="",4)+IF(H8="",4)+IF(I8="",4)+IF(J8="",4)+IF(K8="",4)+IF(L8="",4)+IF(M8="",4)+IF(N8="",4)+IF(O8="",4)+IF(P8="",4)+IF(Q8="",4)+IF(R8="",4)+IF(S8="",4)+IF(T8="",4)+IF(U8="",4)+IF(V8="",4)+IF(W8="",4)+IF(X8="",4)+IF(Y8="",4)+IF(Z8="",4)+IF(AA8="",4)+IF(AB8="",4)+IF(AC8="",4)+IF(AD8="",4)+IF(AE8="",4)+IF(AF8="",4)+IF(AG8="",4)+IF(AH8="",4)+IF(AI8="",4)+IF(AJ8="",4)
 
Try this array* formula:

=SUM(IF(G8:AJ8="",4,G8:AJ8))

* An array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE) rather than the usual <Enter>. If you do this
correctly then Excel will display curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit or amend the formula you will need to use CSE again.

Hope this helps.

Pete
 
Non-array formulas
=SUM(G8:AJ8)+COUNTIF(G8:AJ8,"")*4
or
=SUM(G8:AJ8)+COUNTBLANK(G8:AJ8)*4
best wishes
 
Back
Top