Hello,
In the attached file, I'd like to be able to total the numeric values that preceed certain alpha values in text strings. For example, Item #1 includes "5P" while Item #4 includes "1P", so B6 would be 6. I've played with search and mid, but can't figure out how to iterate through multiple rows and keep a running total (without VB).
Thanks,
Adam
With text to column.
After that I run the macro below.
After that I made an pivot table of it.
See the attached file.
Please reply.
Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
We're not supposed to run macros on files in my workplace. Is there a way to do this without them?
Maybe by others.Please Login or Register to view this content.
Try this array formula
=SUM(IFERROR(MID(B1:B4,FIND("P",B1:B4)-1,1),0)*1)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Change the highlighted portion for varied results
Life's a spreadsheet, Excel!
Say thanks, Click *
With the solution of Ace_XL in the file.
Money! thank you. I could have sworn I tried to reference the range, but it didn't work...guess not.
Who is money ?Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks