+COUNTBLANK

[Ernie]

Hi all,

I have =(25-(COUNTBLANK(F2:AD2)))*10
This works fine but I have added another column after each
of the columns in the above formula and I need to only
count every other column starting at F2 thru BB2 for a
total of 25 columns so that the above formula will work.
I think my problem is in the (F2:AD2) section. How can I
write this to skip columns, ie:

=(25-(COUNTBLANK(F2,H2,J2,L2, etc to BB2)))*10

thanks, Ern

 

[Peo Sjoblom]

One way

=25-(SUMPRODUCT(--(MOD(COLUMN(F2:BB2),2)=0),--(F2:BB2="")))*10

 

[Bob Phillips]

Small correction to Peo's solution, he is only multiplying the count by 10,
not 25 minus the count. It should read

=(25-(SUMPRODUCT(--(MOD(COLUMN(F2:BB2),2)=0),--(F2:BB2=""))))*10

This can also be wriitten, more succinctly (IMO) as

=(25-(SUMPRODUCT((MOD(COLUMN(F2:BB2),2)=0)*(F2:BB2=""))))*10

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)