How to count interstitial blank cells?
How can I count the number of blank cells between
the row above and the last nonblank row above, if the cell in this row is nonblank? That is, the count is one or more only in the first nonblank cell following one or more blank cells. For example, suppose A1:A2 are nonblank, A3:A5 are blank, and A6:A9 are nonblank. I want B6 to count 3 and B10 to count 4. All other cells in B:B should be blank (or zero). I know I can do this easily in VBA. Is there some way to write a cell formula that only uses built-in Excel functions -- preferrably not even add-ins? |
How to count interstitial blank cells?
Bryan Hessey wrote:
testing column A, and using column C as a helper column, in C2 put =IF(A2<"","",IF(C1="",1,C1+1)) and in B2 put =IF(A3="","",C2) and formula drag B2 and C2 down to cover your data. Well, I think I screwed up my example, but I got the idea. Works great! Thanks. |
How to count interstitial blank cells?
Good to see it works for you, and thanks for the response -- Wrote: Bryan Hessey wrote: testing column A, and using column C as a helper column, in C2 put =IF(A2<"","",IF(C1="",1,C1+1)) and in B2 put =IF(A3="","",C2) and formula drag B2 and C2 down to cover your data. Well, I think I screwed up my example, but I got the idea. Works great! Thanks. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=533202 |
All times are GMT +1. The time now is 10:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com