Counting columns

[ChrisMcIntyre]

Hello!

I am trying to come up with a method of counting the number of columns in a changeable range.

I have three years on a sheet. Each year has 52 columns (52 weeks).
In the last row I have a summary of the total numbers in each column.

What I need to know is how many weeks, from the first column with data in to the last column of data, including any unused weeks, there are in total.

So as an example, if there was a value >0 in all but 10 of the weeks for that year, the total number of weeks with something in is 42, but the range I am after is actually 48. That's 48 weeks from the first column with data to the last column with data.

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
4		0	0	0	0	0	1	0	0	0	1	1	0	0	0	1	0	0	0	0	0
5	10
Sheet24

Cell Formulas
Range	Formula
A5	A5	=MAX((B4:U4>0)*COLUMN(B4:U4))-MIN(IFERROR(1/(1/((B4:U4>0)*COLUMN(B4:U4))),""))+1

 

[queuesmef]

i don't follow you. Esp on the 48.
you could use the =Count formula to see/count the number of cells that have a number in them(it won't count the blank cells).
But it looks like you're after the last week used in the year? please elaborate

 

[ChrisMcIntyre]

Thanks for the speedy response both.

So in my screen grab below I have 1 year or 52 weeks reflected across 52 columns.
At the bottom of each column there's a total, reflecting the number of units above in each column.

In this example, the plan starts in week 15 and ends in week 39. What I want to know is how many columns are in the range W15 to W39. In other words, how long is this plan for, and the answer in this case is 24 weeks. W26 has no un its in, but is within the range of the first week and the last week so is counted.

 

[ChrisMcIntyre]

In essence, I see when the first column has data and when the last column has data, and if I were to select across that range I would see a "Count" of 24. The selection includes blanks, it's just counting between first & last.

If I were to put a unit in week 1 and a unit in week 52, the count would be 52, as the units start in week 1 and end in week 52.

 

[Fluff]

For 365 how about

=LET(a,FILTER(COLUMN(B6:BA6),B6:BA6>0),MAX(a)-MIN(a)+1)

 

[ChrisMcIntyre]

Thanks Fluff

I put that in A6 and got 25?

 

[ChrisMcIntyre]

Oh sorry, being very slow today, 25 is perfect!

Thanks so much fluff!!

 

[Fluff]

Glad we could help & thanks for the feedback.