Formula to count highest number of continuous cells w/ data in a range and their sum

**orphis** · 04-01-2009, 08:58 PM

So I need some help and could not find this question anywhere. I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.

Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.

I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.

Thanks,

Brian

**TonyB51** · 04-01-2009, 09:59 PM

Hi,

I think the attached sample may be what you are looking for.

It is not fancy but could do the Job.

**TonyB51** · 04-01-2009, 10:06 PM

Hi, Again

I just realised that you also wanted their sum.

I have modified the sheet to do this.

Cheers

Tony

**orphis** · 04-01-2009, 10:10 PM

Yep, that will do it and without too much fanciness, Sometimes you just get use to the long, long formulas and the simple ones escape you. Thanks!!

**TonyB51** · 04-01-2009, 10:17 PM

Hi again,

I also realised that the Maximum amount is not linked to the maximum count.

The attached amendment with a sumif statement will fix it.

Cheers

Tony

**orphis** · 04-01-2009, 10:25 PM

Slight problem I see is that max amnount does not work if you have two ranges with the same max count, e.g. it adds the values for both ranges then. Any thoughts on that problem?

**TonyB51** · 04-02-2009, 12:50 AM

Hi, Again

Okay, I have made it a lookup.

You can expand as necessary.

Cheers

Tony

**chaitanya sagar** · 04-02-2009, 02:02 AM

Tony, that's good work.

**littlekid** · 02-24-2010, 07:15 AM

Originally Posted by tonyb51

hi,

i think the attached sample may be what you are looking for.

It is not fancy but could do the job.

hello there
i need your help
i try to do this one in vertical mode but i can't

i would like to explain me in a excel sheet
how can i have the total of cells that include number in a row

like
i have numbers and letters in a row
i would like to have a result in a new column how many cells include only numbers in a row

a b c d e f g h i j k l m n o
1 1 1 1 2 1 2 6 a c 1 3 4 5 1 a
2
3

so the right result is 7 numbers in a row
can you help me with this one

thanks in advance

**DonkeyOte** · 02-24-2010, 07:20 AM

littlekid, if you have a question please ask it in your own thread.

Formula to count highest number of continuous cells w/ data in a range and their sum

LinkBack

Thread Tools

Display

Formula to count highest number of continuous cells w/ data in a range and their sum

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Re: Formula to count highest number of continuous cells w/ data in a range and their

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions