Count of span of cell activity

dovl

New Member
Joined
Apr 6, 2021
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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!

1620416530926.png
 

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
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Apr 6, 2021
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,967
Try this in V2:

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top