Count Consecutive Numbers in a Row

  • Thread starter Thread starter Sam via OfficeKB.com
  • Start date Start date
S

Sam via OfficeKB.com

Hi All,

Can anyone help me with a Formula to COUNT how many numbers are consecutive
within a Row that spans 10 Columns?

Example:
65 67 68 69 75 79 80 84 85 90

The answer to the above example should be a Count of 7.

Much appreciated.

Regards,
Sam
 
I may have just misread this, but how do you get 7? I read consecutive as
increments of 1?

67/68 = 1
68/69 = 1
79/80 = 1
84/85 = 1
---
4

Assuming data in A1:J1

=SUMPRODUCT(--((B1:J1)-(A1:I1)=1))
 
Hi Ken,

Thanks for reply. Apologies for the confusion.

Yes, I agree, consecutives are increments of 1.

I should have elaborated further to say that I require a total count of the
individual numbers that make up the consecutive. So, rather than 67/68 = 1
or 79/80 = 1.

I need 67,68,69 = 3
79,80 = 2
84,85 = 2
---
Total Count 7

Example:
65 67 68 69 75 79 80 84 85 90

Is there a way that a Formula can be put together to Count the above and
arrive at total Count = 7?

Any further assistance much appreciated.

Regards,
Sam
 
To simplify the formulas, start your 10 numbers in column B or farther to the
right.

Assuming your numbers are in B1:K1, in B2 put this formula:

=IF(OR(B1=A1+1,B1=C1-1),1,0)

and copy it across through K1. The number of consecutive entries =SUM(B2:K2)

If your data is in A1:J1, you can't use the above formula in A2 because
there's no cell to the left of A1. The formulas for B2:J2 would be as above;
the formula for A2 would be

=IF(A1=B1-1,1,0)
 
Hi Myrna,

Thank you very much for all your help - most appreciated.
Your suggested Formula did the job.

Regards,
Sam
 
Back
Top