count blanks until multiple criteria occurring after 1st blank

**stew_man** · 03-10-2011, 09:04 PM

Please help, I’m having trouble.

I need two general formulas to count blanks until multiple criteria occurring after 1st blank

A B
1 yellow fall
2 yellow spring
3 red fall
4 yellow
5 red
6 red spring
7 yellow spring
8 red
9 yellow

I need one formula for blanks between 1st blank and 1st occurrence of red and blank after 1st blank and one formula for blanks between 1st blank and 2nd occurrence of red and blank after 1st blank.

1st blank (row 4)
1st occurrence = 1 (row 5)
2nd occurrence = 2 (row 8)

Thanks in advance.

**stew_man** · 03-10-2011, 09:06 PM

Sorry, my table got bunched together.

**DonkeyOte** · 03-11-2011, 05:57 AM

If I've understood

Please Login or Register  to view this content.

Note the Array entry requirement

**stew_man** · 03-11-2011, 01:35 PM

No. Sorry my table got jumbled up and I am new to this forum but let's see if I can clear up.

column A is a list of colors pre-filled from row 1-100 (every cell A1 to A100 has the name of a color in it).

column B is currently sparsely populated with seasons (i.e. currently only every 5th or 6th cell has the name of a season in it).

Now, column B will start to be filled-in with seasons starting at the top in B1 and then B2, B3, B4, etc. but remember that some of the cells in column B already have the name of seasons in them.

I want a formula in C1 to keep a running count of the number of blank cells in column B, starting with the 1st occurrence of a blank as 1 (If B1, B2, B3, B4, have seasons in them and B5 doesn’t then B5 would be the 1st occurrence of a blank) with the count stopping the FIRST time the color “Red” appears in column A which also has a blank next to it in column B (remember there is a possibility of a “Red” in column A already having the name of a season next to it in column B. This would NOT count as an occurrence).

2nd formula would be in C2 to keep a running count of the number of blank cells in column B, starting with the 1st occurrence of a blank just like the formula in C1 but would count blanks in column B with the count stopping the SECOND time the color “Red” appears in column A which also has a blank next to it in column B.

Thanks so much if you could help

**DonkeyOte** · 03-11-2011, 03:01 PM

That's exactly what the formula provided gives you - if you think otherwise please post a sample with formula in place to illustrate.

**stew_man** · 03-11-2011, 04:41 PM

You are so right. I missed that the formula was dependent on the cell I put it in and that copying down would actually change the 2nd formula to give me the 2nd occurance. Thank-you so much.

Chris