Hello all,
I have a huge amount of sales data (sample in the attachment). Currently, I fill column A (= sold amount of 'No. 503 Ginger Delight BIO - 1 kg') manually. Obviously this takes me a lot of time
So I am looking for a way to automatically extract the sold amount for a specific product. In this case 'No. 503 Ginger Delight BIO - 1 kg'.
So the request for help is: how can I automatically fill column A for each row?
I hope somebody can help me.
Thanks a lot!
Kind regards,
Kelly
16.80??? what is the Excel PRODUCT that you are using? O365, Excel 2021, etc, etc.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn,
Thanks for your question.
It's Microsoft 365, Microsoft Excel for Mac, version 16.80.
Kelly
Kindly amend your profile, so that it is CLEAR.
1. Change a1 to contain ONLY the name of the product you're looking for.
2. There are some UNICODE characters in the data columns that threw me off.. the x after the number is not a regular x.
3. Use:
=SUM(--TOCOL(TEXTBEFORE(IF(ISNUMBER(SEARCH(TRIM($A$1),TRIM(F2:M2))),F2:M2,""),UNICHAR(215)),3))
or for a dynamic array (delete all expected results first):
=BYROW(F2:M4,LAMBDA(x,SUM(--TOCOL(TEXTBEFORE(IF(ISNUMBER(SEARCH(TRIM($A$1),TRIM(x))),x,""),UNICHAR(215)),3))))
Hi Glenn,
It works! Impressive, thank you so much!
Now that I see the formula I'm sure I couldn't have solved it myself
One question: how do I adjust the formula to a larger array? For example, for a spreadsheet with product data in array F2:Z650?
Thanks,
Kelly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks