# Count of span of cell activity

#### dovl

##### New Member
Hi, I am looking for a formula to count the number of cells from the first cell with a number in it, through the last cell with a number in it, including blanks. My spreadsheet will have dates across the top row. These numbers represent how many times the device was used in a given days, and I'm trying to determine how many days the device was active including the dormant days. In My example highlighted in the spreadsheet pasted below, Im looking for a formula in cell V7 that will search the range (B7:R7), identify the first date of use i.e 1.23.21 (you can reference the date in row 1) and count the span of days/cells through its last day of activity i.e. 2.1.21 and produce the number of days "10". Technically I don't need to reference days, I'm just looking for the number of cells, but being that there are blank cells, It may make it hard to create a formula around that.

Thanks!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(COUNTIFS(B2:R2,"<>")=0,0,MAXIFS(\$B\$1:\$R\$1,B2:R2,"<>")-MINIFS(\$B\$1:\$R\$1,B2:R2,"<>")+1)``

#### dovl

##### New Member
Hmm that resulted in all the cells in column V returning 1 except row 3 yielded Zero. I tried downloading the XL2bb but having trouble with that. Thank you.

#### Eric W

##### MrExcel MVP
Try this in V2:

Excel Formula:
``=IFERROR(SUM(AGGREGATE({14,15},6,COLUMN(B2:R2)/(B2:R2>0),1)*{1,-1})+1,0)``

#### dovl

##### New Member
Try this in V2:

Excel Formula:
``=IFERROR(SUM(AGGREGATE({14,15},6,COLUMN(B2:R2)/(B2:R2>0),1)*{1,-1})+1,0)``
Thank you!! That worked!!

Replies
17
Views
222
Replies
0
Views
189
Replies
3
Views
57
Replies
31
Views
335
Replies
5
Views
70

1,133,271
Messages
5,657,765
Members
418,411
Latest member
Excellency

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back