# How to count consecutive columns

1. ## How to count consecutive columns

Dear all,

Once again I turn to the excellent forum for help with a simple excel problem.

I have a worksheet in excel 2007. The sheet has several hundred rows, and about 100 columns.
For each row there are many blank entries.

i need to know for each row, how many consecutive columns contain data.

I'm sure this is easy, but I can't think of how to do it simply.

I've attached a dummy, simplified worksheet to illustrate the problem.

Thanks,
A

2. ## Re: How to count consecutive columns

Hi,

In cell N2, write the following:

=COUNT(B2:M2)

and paste down.

Thank you.

Kind Regards,
theabdulrab.

3. ## Re: How to count consecutive columns

Maybe this: =SUMPRODUCT(--(B2:M2<>""),--(A2:L2<>""))-(B2<>"")

4. ## Re: How to count consecutive columns

Thanks for the quick replies. The sumproduct formula didn't work. It got it right in some rows, but not in others...

5. ## Re: How to count consecutive columns

Can you tell us in what did work (or didn't) or we need to guess

6. ## Re: How to count consecutive columns

oops, sorry.
If you apply the formula, the first row value is 5, but there are only a maximum of 3 consecutive columns containing data in that row.

Thanks,
A

7. ## Re: How to count consecutive columns

Check now...

Notice that I need to add two columns B and O (and then I left them empty hide them)

8. ## Re: How to count consecutive columns

Hi

Is this Array(Control+Shift+Enter) formula, works for you?

=MAX(FREQUENCY(IF(B2:M2>=1,COLUMN(B2:M2)),IF(B2:M2<1,COLUMN(B2:M2))))

9. ## Re: How to count consecutive columns

It give slightly me different results at the end... Hope at least one solution is good

10. ## Re: How to count consecutive columns

Thanks for the quick response.

This works for the first row, but again there is a problem further down.

For example, look at the last row: it gives a value of 2, but there are 4 consecutive columns with data in that row.

Sorry, this is a tricky problem.

Thanks again,
A

11. ## Re: How to count consecutive columns

Then Fotis solution will work for you.

I understood this as there are 2 consecutive columns in last row: 1,1 and 1,4,5,1 as 2 pairs of consecutive columns.

12. ## Re: How to count consecutive columns

dummy sheet.xlsx

Hi,

Does the attached file serve the purpose?

Thank you.

Kind Regards,
theabdulrab.

13. ## Re: How to count consecutive columns

Thanks all. Fotis' solution worked perfectly for me.

What an excellent forum!

A

14. ## Re: How to count consecutive columns

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1