How can I count groups of blanks?

[Kewa]

Hi

I have a spreadsheet with a large area of data and Col H contains the formula... =IF(OR(OR(D1<>D2,E1<>E2,F1<>F2)),"CHANGE","")

So Col H now contains "CHANGE" or is blank

I want to add a formula to Col I so that if adjacent cell in Col H = "CHANGE" it returns a count of the blank cells between that incidence of "CHANGE" and the previous incidence of "CHANGE" above

CHANGE
""
""
""
""
CHANGE 4
""
""
""
""
""
""
CHANGE 6

etc...

I'm sure there's an easy and logical answer but I just can't see it so any help would be much appreciated

Many thanks

Kewa

[Pete_UK]

No file to work with? It makes it a lot easier for us (see yellow banner at the top of the screen).

In the absence of a sample file, try this in cell I2:

=IF(H2="","",ROW()-COUNTIF(H$1:H2,"Change"))

Copy down as required.

Hope this helps.

Pete

[Kewa]

Thanks a lot Pete

Sorry for the lack of a file but it's a big one with 35000+ rows of data so I thought my description would be clear.

The formula almost works but returns a cumulative count of blanks rather than an individual count between each incidence of "CHANGE" e.g. if there are 6 blanks between the 1st and 2nd CHANGE it returns 6 but if there are 8 blanks between the 2nd and 3rd CHANGE it returns 14 instead of 8 and so on.

Any thoughts on a formula for a separate count of each group of blanks

Thanks again

Kewa

[Pete_UK]

Just subtract the actual values above the cell, i.e. in I2:

=IF(H2="","",ROW()-COUNTIF(H$1:H2,"Change")-sum(I$1:I1))

I would have spotted that if I was playing about with a sample file (which only needs to be about 20 rows or so).

Hope this helps.

Pete

[Pete_UK]

By the way, are you still using XL2003, as your profile states? If not, please update your profile on User CP.

Pete

[Kewa]

....=IF(H2="","",ROW()-COUNTIF(H$1:H2,"Change")-sum(I$1:I1))

That's fantastic, thanks a lot. I would never have worked that out.

By the way, are you still using XL2003, as your profile states?...

I'm afraid so. I used to use XL (2003) a lot for work until I retired in 2006 but have barely needed it since, hence my being challenged by the formula after so long, and haven't felt the need for a newer version

Many thanks again for your help and very speedy responses - it's much appreciated

Kewa

[Pete_UK]

Glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete