Count of span of cell activity

[dovl]

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!

 

[Fluff]

How about

=IF(COUNTIFS(B2:R2,"<>")=0,0,MAXIFS($B$1:$R$1,B2:R2,"<>")-MINIFS($B$1:$R$1,B2:R2,"<>")+1)

 

[dovl]

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]

Try this in V2:

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

 

[dovl]

Try this in V2:

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

Thank you!! That worked!!

 

[Eric W]

Glad we could help!

 

[dovl]

Try this in V2:

=I
[QUOTE="Eric W, post: 5687924, member: 338656"]
Try this in V2:

[CODE=xls]=IFERROR(SUM(AGGREGATE({14,15},6,COLUMN(B2:R2)/(B2:R2>0),1)*{1,-1})+1,0)

If I want to paste the provided formula in the expanded spreadsheet (Xl2bb pasted below) what modifications to the formula must I make? I added 3 columns before the data set and this is causing the result to be overstated by 3. Please advise what else I need to be sensitive to if my data sets dates are expanding or columns are being added before my data set. Thanks
[/QUOTE]

Counts (34).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL
1	Meter ID	Facility	First	Last	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15	2021-03-16	2021-03-17	2021-03-18	2021-03-19	2021-03-20	2021-03-21	2021-03-22	2021-03-23	2021-03-24	2021-03-25	2021-03-26	2021-03-27	2021-03-28	2021-03-29	2021-03-30	2021-03-31	2021-04-01	2021-04-02	2021-04-03	2021-04-04	2021-04-05	2021-04-06	2021-04-07	2021-04-08	2021-04-09	2021-04-10	2021-04-11	2021-04-12	2021-04-13	2021-04-14	2021-04-15	2021-04-16	2021-04-17	2021-04-18	2021-04-19	2021-04-20	2021-04-21	2021-04-22	2021-04-23	2021-04-24	2021-04-25	2021-04-26	2021-04-27	2021-04-28	2021-04-29	2021-04-30	2021-05-01	2021-05-02	2021-05-03	2021-05-04	2021-05-05	2021-05-06	2021-05-07	2021-05-08	2021-05-09	2021-05-10	2021-05-11	2021-05-12	2021-05-13	2021-05-14	2021-05-15	2021-05-16	2021-05-17	2021-05-18	2021-05-19	2021-05-20	2021-05-21	2021-05-22	2021-05-23	2021-05-24	2021-05-25	2021-05-26	2021-05-27	2021-05-28	2021-05-29	2021-05-30	2021-05-31	2021-06-01	2021-06-02	2021-06-03	2021-06-04	2021-06-05	2021-06-06	2021-06-07	2021-06-08	2021-06-09	2021-06-10	2021-06-11	2021-06-12	2021-06-13	2021-06-14	2021-06-15	2021-06-16	2021-06-17	2021-06-18	2021-06-19	2021-06-20	2021-06-21	2021-06-22	2021-06-23	2021-06-24	2021-06-25	2021-06-26	2021-06-27	2021-06-28	2021-06-29	2021-06-30	2021-07-01	2021-07-02	2021-07-03	2021-07-04	2021-07-05	2021-07-06	2021-07-07	2021-07-08	2021-07-09	2021-07-10	2021-07-11	2021-07-12	2021-07-13	2021-07-14	2021-07-15	2021-07-16	2021-07-17	2021-07-18	2021-07-19	2021-07-20	2021-07-21	2021-07-22	Total	Count	Span of days
2	0657562	Unnassigned	Unnassigned	Unnassigned			2	3	2	3	3	3	3	3	5	3	3	3	3	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	2	3	3	3	3	3	3	3			2	3	3	2	3	3	3	3	1	3	3	3	3	3	4	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	4	3	3	1	4	3	3	3	3	3	4	3	2																																											1	3		1	1		2	2	3	2	3			1	1				1	3		2	2	1	2	1										348	124	188
3	0661648	Unnassigned	Unnassigned	Unnassigned																												1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	199	156	163
4	0661655	Unnassigned	Unnassigned	Unnassigned																																			1	1	1	1	1	1	1	1	1		1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1																																																																																1									5				7	1	1	2	4	4				1																						54	37	156
5	0675861	Unnassigned	Unnassigned	Unnassigned				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1									1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1							1	1	1	1	1	1	1	1	1	1	1	165	165	187
6	0774458	Unnassigned	Unnassigned	Unnassigned																																																																																																																																																																																												0	0	0
Excel forum

Cell Formulas
Range	Formula
GK2:GK6	GK2	=COUNT(E2:GI2)
GL2:GL6	GL2	=IFERROR(COLUMN($GI$2)-AGGREGATE(15,6,(COLUMN($E$1:$GI$1)-COLUMN($E$1)+1)/(E2:GI2<>""),1),0)

FERROR(SUM(AGGREGATE({14,15},6,COLUMN(B2:R2)/(B2:R2>0),1)*{1,-1})+1,0)[/CODE]

 

[dovl]

If I want to paste the provided formula in the expanded spreadsheet (Xl2bb pasted below) what modifications to the formula must I make? I added 3 columns before the data set and this is causing the result to be overstated by 3. Please advise what else I need to be sensitive to if my data sets dates are expanding or columns are being added before my data set. Thanks

Counts (34).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL
1	Meter ID	Facility	First	Last	2021-01-17	2021-01-18	2021-01-19	2021-01-20	2021-01-21	2021-01-22	2021-01-23	2021-01-24	2021-01-25	2021-01-26	2021-01-27	2021-01-28	2021-01-29	2021-01-30	2021-01-31	2021-02-01	2021-02-02	2021-02-03	2021-02-04	2021-02-05	2021-02-06	2021-02-07	2021-02-08	2021-02-09	2021-02-10	2021-02-11	2021-02-12	2021-02-13	2021-02-14	2021-02-15	2021-02-16	2021-02-17	2021-02-18	2021-02-19	2021-02-20	2021-02-21	2021-02-22	2021-02-23	2021-02-24	2021-02-25	2021-02-26	2021-02-27	2021-02-28	2021-03-01	2021-03-02	2021-03-03	2021-03-04	2021-03-05	2021-03-06	2021-03-07	2021-03-08	2021-03-09	2021-03-10	2021-03-11	2021-03-12	2021-03-13	2021-03-14	2021-03-15	2021-03-16	2021-03-17	2021-03-18	2021-03-19	2021-03-20	2021-03-21	2021-03-22	2021-03-23	2021-03-24	2021-03-25	2021-03-26	2021-03-27	2021-03-28	2021-03-29	2021-03-30	2021-03-31	2021-04-01	2021-04-02	2021-04-03	2021-04-04	2021-04-05	2021-04-06	2021-04-07	2021-04-08	2021-04-09	2021-04-10	2021-04-11	2021-04-12	2021-04-13	2021-04-14	2021-04-15	2021-04-16	2021-04-17	2021-04-18	2021-04-19	2021-04-20	2021-04-21	2021-04-22	2021-04-23	2021-04-24	2021-04-25	2021-04-26	2021-04-27	2021-04-28	2021-04-29	2021-04-30	2021-05-01	2021-05-02	2021-05-03	2021-05-04	2021-05-05	2021-05-06	2021-05-07	2021-05-08	2021-05-09	2021-05-10	2021-05-11	2021-05-12	2021-05-13	2021-05-14	2021-05-15	2021-05-16	2021-05-17	2021-05-18	2021-05-19	2021-05-20	2021-05-21	2021-05-22	2021-05-23	2021-05-24	2021-05-25	2021-05-26	2021-05-27	2021-05-28	2021-05-29	2021-05-30	2021-05-31	2021-06-01	2021-06-02	2021-06-03	2021-06-04	2021-06-05	2021-06-06	2021-06-07	2021-06-08	2021-06-09	2021-06-10	2021-06-11	2021-06-12	2021-06-13	2021-06-14	2021-06-15	2021-06-16	2021-06-17	2021-06-18	2021-06-19	2021-06-20	2021-06-21	2021-06-22	2021-06-23	2021-06-24	2021-06-25	2021-06-26	2021-06-27	2021-06-28	2021-06-29	2021-06-30	2021-07-01	2021-07-02	2021-07-03	2021-07-04	2021-07-05	2021-07-06	2021-07-07	2021-07-08	2021-07-09	2021-07-10	2021-07-11	2021-07-12	2021-07-13	2021-07-14	2021-07-15	2021-07-16	2021-07-17	2021-07-18	2021-07-19	2021-07-20	2021-07-21	2021-07-22	Total	Count	Span of days
2	0657562	Unnassigned	Unnassigned	Unnassigned			2	3	2	3	3	3	3	3	5	3	3	3	3	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	2	3	3	3	3	3	3	3			2	3	3	2	3	3	3	3	1	3	3	3	3	3	4	3	3	3	3	4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	4	3	3	1	4	3	3	3	3	3	4	3	2																																											1	3		1	1		2	2	3	2	3			1	1				1	3		2	2	1	2	1										348	124	188
3	0661648	Unnassigned	Unnassigned	Unnassigned																												1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	199	156	163
4	0661655	Unnassigned	Unnassigned	Unnassigned																																			1	1	1	1	1	1	1	1	1		1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1																																																																																1									5				7	1	1	2	4	4				1																						54	37	156
5	0675861	Unnassigned	Unnassigned	Unnassigned				1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1					1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1									1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1							1	1	1	1	1	1	1	1	1	1	1	165	165	187
6	0774458	Unnassigned	Unnassigned	Unnassigned																																																																																																																																																																																												0	0	0
Excel forum

Cell Formulas
Range	Formula
GK2:GK6	GK2	=COUNT(E2:GI2)
GL2:GL6	GL2	=IFERROR(COLUMN($GI$2)-AGGREGATE(15,6,(COLUMN($E$1:$GI$1)-COLUMN($E$1)+1)/(E2:GI2<>""),1),0)

[/QUOTE]
Sorry. I pasted the wrong formula. It seems the formula you provided is not sensitive to adding columns before the data set.

 

[Eric W]

Try the formula I provided in post 4, changing the columns as necessary:

=IFERROR(SUM(AGGREGATE({14,15},6,COLUMN(E2:GI2)/(E2:GI2>0),1)*{1,-1})+1,0)