LinkBack URL
About LinkBacks
Good morning everyone,
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
Thank you,
Roberto Lucesi, The Netherlands
Good morning everyone,
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
Thank you,
Roberto Lucesi, The Netherlands
Last edited by roberto1111; 12-29-2009 at 03:08 AM.
maybe like this
=SUMPRODUCT(--(B6:B11=(B5:B10)+1))
Try this:
Does that help?
Please Login or Register to view this content.
Good day Teylyn and Ron,
I must apologize, I made a wrong example here.
The consecutive values al spread over a column, in stead of following up to one another.
Please take a look at the right example I've just attached.
Thank you for your time,
Roberto Lucesi
Hmm.. maybe with a helper column in Column C
=SUMPRODUCT(--($B$9:$B$17=B9+1))
copied down, and then in B7
=SUM(C8:C17)
The helper column can be hidden.
A helper column on the side would be a problem since there is'nt space on the actual sheet to do so, there's only space above the columns.
Any ideas?
You mean you've already used all available 16384 columns?? Why do I find it hard to believe that....?
You could have the helper column on a separate sheet, say column A on Sheet2 and then change the formula to be
=SUMPRODUCT(--(Sheet1!$B$9:Sheet1!$B$17=Sheet1!B9+1))
and copy down, and then do in B7
=SUM(Sheet2!A8:A17)
adjust sheet names and ranges to suit.
hth
Hi
Try:
=SUM(0+(B9:B17-TRANSPOSE(B9:B17)=1))
This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.
Re: Count Consecutive Values
Good day Lecxe,
The arrayformula works perfectly! Thanks a lot for helping me out.
Teylyn en Ron also thank you for your time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks