I need to count consecutive values in a row that is interspersed with blank cells

**mrideout** · 10-28-2014, 06:24 PM

Greetings Everyone!

This is my first time; please be gentle. Does anyone know how I can count (within a row) the largest number of consecutive cells that are not blank? For instance, among multiple rows of data, row 1 has 10 consecutive cells that aren't blank, row 2 has 15, row 3 has 12, etc. I have hundreds of rows and the blanks are never in the same place. "Count" is returning the total number of cells that have values, but not the maximum number of consecutive values with in that row; that is what I need.

I have attached a sampling.

Thanks in advance!!

-m

**FlameRetired** · 10-28-2014, 11:27 PM

My impressions are that this is going to require a VBA / macro solution.

With that in mind you might do well if you contact one of the ExcelForum moderators and request they move your thread to that section.

Hope this helps.

**shg** · 10-29-2014, 12:12 AM

Row\Col	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
1	TIME	0	1	2	3	4	5	6	7	8	9	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
2	Sample X1						801	796	761	713	652																				5
3	Sample X2								662	637	585																				3
4	Sample X3	116								566	534	497	484	451																	5
5	Sample X4												519																		1
6	Sample X5								599	601	588	564	543	503	467																7
7	Sample X6				577	676	733	711	660	639	621	576	555	534	502	468	454	440	428	419	401	393	383	375	370	365	360	357	354		25
8	Sample X7																						118	117	113	114	109	106	106		7
9	Sample X8	-194	-211																												2
10	Sample X9						672	685	693	673	633	588	533																		7
11	Sample X10	798		622	701	722	782	823	798	766	686	644	601	563																	11
12	Sample X11							552	566	618	590	556	523	490																	7
13	Sample X12								660	671	680	664	637	592	563	523															8

In AE2, confirmed with Ctrl+Shift+Enter and then copied down,

=MAX(FREQUENCY(IF(B2:AC2<>"", COLUMN(C2:AD2)), IF(B2:AC2="", COLUMN(B2:AD2))))

**FlameRetired** · 10-29-2014, 01:36 AM

@shg
Good one. The offset columns idea never occurred to me. Thanks for sharing this one.

**shg** · 10-29-2014, 04:59 PM

In response to PM about posting BBCode (not HTML): The add-in is at https://app.box.com/s/soezox25h3w0q5s4rcyl

**mrideout** · 10-29-2014, 05:01 PM

Thanks so much SHG!! This worked perfectly!!

**shg** · 10-29-2014, 05:02 PM

You're welcome.

I need to count consecutive values in a row that is interspersed with blank cells

LinkBack

Thread Tools

Rate This Thread

Display

I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Re: I need to count consecutive values in a row that is interspersed with blank cells

Thread Information

Users Browsing this Thread

Similar Threads

Formula to Count Consecutive Blank Cells in a Range Depending on Today's Date

Count Consecutive Empty Cells and Delete non-blank cells by specified number of days.

Macro to count consecutive blank cells and assign a number

Excel 2008 : How to Count and/or Sum consecutive values on a range with blank data in the middle

Excel 2007 : Count number of consecutive blank cells using script

Bookmarks

Bookmarks

Posting Permissions