How can I get Excel to number only the rows that start with a specified word or phrase? Though, it needs to count them up as 1,2,3,4, and not just place a 1 for each row, which is where I'm stuck at using the following formula:
Is this even possible with a formula, or would I need to use VBA?
Please Login or Register to view this content.
Thank you!
Try:
=SUMPRODUCT((LEFT(C3:C10,9)="Published")*(C3:C10<>""))
Change the range to suit.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
how about
=IF(LEFT(C3,9)="Published",COUNTIF($C$3:C3,"Published"),"")
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks for the super quick suggestion, though using that formula just gives me the total for all the entries.
I need each row numbered sequentially so that when I print the list, each section will be easily referenced.
Last edited by AliGW; 04-14-2024 at 01:08 PM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Provide a sample workbook with a manual mock-up of what you want.
That will only work if "Published" is the only criteria, but it's not. As I mentioned in my OP, "rows that start with a specified word or phrase."Originally Posted by etaf
Though I do see it is numbering sequentially, so definitely on the right track. Just need to modify it to work with StartWith if that is possible in a formula.
No need, ETAF got it figured out. Just need to tweak it to work with "startswith" rather than "equal to".
Thanks again for trying to help. Much appreciated!
That was an accident, I meant to quote you, but apparently clicked on mine some how. After I realized it, I tried to remove it, but apparently I'm not allowed to or just blind and couldn't find the delete button. So, I tried deleting the message in the post, but of course, it then said my post was too short. Just my luck! Can you remove the post?
What the heck! Why are all my replies going back to myself? I know I'm not clicking on myself this many times. This is crazy! I'm going to logout and back in.
you are welcome
it would be worth posting the formula you actually usedJust need to tweak it to work with "startswith" rather than "equal to".
That way anyone searching the forum or google will see the formula which worked for your question
I think you misunderstood me, as I still don't have a working formula yet. I was hoping you knew how to edit the formula you provided to work with a value that "starts with Published" not that equals "Published".
ignore for a minute - wrong answer
=IF(ISNUMBER(SEARCH("Published",C3,1)),COUNTIF($C$3:C3,"*"&"Published"),"")
Actually, I got it work using your previous formula, though I just added a * after Published, which makes it work as StartsWith.
Thank you for your help and quick replies!Please Login or Register to view this content.
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks