I am looking for a formula that resets itself. On each row called Country, it is one in column B. For each non blank row in column C after the country row the number is increased by one. When it comes to the next country row, it goes back to 1 and continues the same formula. The format is not always going to be exactly the same. I am looking for a formula solution.
Last edited by hiker100; 09-11-2011 at 02:32 PM.
Hi hiker100,
Try this is C2 and pull it down.
=IF(LEN(B2)>0,1,C1+1)
One test is worth a thousand opinions.
Click the * below to say thanks.
Thanks. This is very close. This is counting blank rows. For blanks rows, I would like it to be 0.
It would be one at country, then 2 for the next non blank row and so on.
How do we just count non blank rows?
Perhaps using a helper say Column E, this can be hidden
In E2
Drag/Fill Down=IF(B2<>"",ROW(),"")
In C2
Drag/Fill Down=IF(B2<>"",1,IF(D2<>"",COUNTA(INDIRECT("$D$"&MAX($E$2:$E2)&":$D"&ROW()))+1,""))
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Yes, thank you. That works great. I will mark it solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks