IF clause for a range of cells

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

Pete_UK

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
 
B

Bernard Liengme

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting Formula 2
Average Every Second Column 15
adding up some cells 5
numbers, text 1
Return a value if a range of cells contain text 2
Using IF and VLOOKUP for range of cells 5
there must be a more simple way of doing this? 2
I want to create a Summary Work Sheet... 2

Top