Siddhi
Reputation: 11
Microsoft Office Excel Count function
In a particular row i want to calculate the number of cells in between the first and the last data For example:
a1- blank
b1 -blank
c1-5
d1-blank
e1-blank
f1-2
g1-blank
h1-blank
i1-8
j1-blank
k1-blank
l1-blank
m1-blank
n1-blank
o1-7
p1-blank
So i want to count the number of cells between C1 and O1
Upvotes: 0
Views: 23
Answers (2)
JNevill
Reputation: 50263
Another option is to use an Array/CSE formula to get the min and max columns that are populated for the row:
=MAX(IF(A1:X1>0, COLUMN(A1:X1))) - MIN(IF(A1:X1>0, COLUMN(A1:X1)))
This gets the max column number and the min column number that have data and subtracts them. Because it's an Array/CSE formula, use Ctrl+Shift+Enter to enter it.
Upvotes: 1
Scott Craner
Reputation: 152650
Use MATCH:
=MATCH(1E+99,A1:P1)-MATCH(TRUE,INDEX(A1:P1<>"",),0)
Change the 1E+99 to "ZZZ" if the values are text instead of numbers.
Upvotes: 1