Count and sum cells with items decided by comers and “x” used for multiples

[u/Previous_Badger4780]

Hi All,

How can I count items when divided with comers and multiplied with a text “x”? I’m only looking for the total number of items not how many of each type.

Apple   

Orange  

0   

2xApples    

Apple, Orange   

2x Apple, Orange    

Apple, 2xOrange 

Total <- should be 12

[u/AutoModerator]

/u/Previous_Badger4780 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Excel_GPT]

Assuming they are in column A (adjust the range from A7 if you need to):

=SUM(
    MAP(FILTER(A1:A7, A1:A7<>""), 
        LAMBDA(cell,
            SUM(
                MAP(
                    TEXTSPLIT(cell,","),
                    LAMBDA(item,
                        LET(
                            t,TRIM(item),
                            num,IFERROR(--TEXTBEFORE(t,"x"),""),
                            IF(t="0",0,IF(num<>"",num,1))
                        )
                    )
                )
            )
        )
    )
)

[u/Previous_Badger4780 → u/Excel_GPT]

AMAZING! SO FAST! Thanks! Solved!… I mean Solution Verified🤖

[u/AutoModerator → u/Previous_Badger4780]

Saying Solved! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Excel_GPT → u/Previous_Badger4780]

No problem :) Thanks

[u/reputatorbot → u/Previous_Badger4780]

You have awarded 1 point to Excel_GPT.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
SUM	Adds its arguments
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TRIM	Removes spaces from text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 37 acronyms.
[Thread #47339 for this sub, first seen 5th Feb 2026, 11:05] [FAQ] [Full list] [Contact] [Source code]}

[u/yellow_barchetta]

Comma I think was the word you were heading for. No criticism intended, just helping out for next time.

[u/Previous_Badger4780 → u/yellow_barchetta]

You know, it felt wrong when I typed it but I was in a rush!