IF clause for a range of cells
 

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)+I F(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)

IF clause for a range of cells
 

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

On Jan 18, 12:21*pm, Al wrote:
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)+I F(J8="",4)+IF(K8="",4)+IF*(L8="",4)+IF(M8="",4)+IF (N8="",4)+IF(O8="",4)+IF(P8="",4)+IF(Q8="",4)+IF(R 8*="",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(AD 8="*",4)+IF(AE8="",4)+IF(AF8="",4)+IF(AG8="",4)+IF (AH8="",4)+IF(AI8="",4)+IF(AJ*8="",4)

IF clause for a range of cells
 

Non-array formulas
=SUM(G8:AJ8)+COUNTIF(G8:AJ8,"")*4
or
=SUM(G8:AJ8)+COUNTBLANK(G8:AJ8)*4
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Al" wrote in message
...
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)+I F(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)

IF clause for a range of cells
 

Try
=SUM(G8:AJ8)+(COUNTIF(G8:AJ8,"")*4)

--
Jacob


"Al" wrote:

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)+I F(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)