CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Nick
Nick

Reputation: 19

How to get SUM of the values of comma separated variables and the variable count in a single spreadsheet cell?

I would like to get the SUM of the values of comma separated variables (Issued Items) in Google Sheet. Please see the table below;

+-----------------------+-----------+
|   Issued Items        |   SUM     |
+-----------------------+-----------+
|   A-22, A-22, B-11    |   120     |
+-----------------------+-----------+
|   C-33, 11, 22-X      |   160     |
+-----------------------+-----------+
|   22-X, D-54, 22      |   110     |
+-----------------------+-----------+

Edited: The Values for each Item will be stored in another sheet. And how can I get the count of Issued items?

Please note that the Items may repeat in a single cell and may also have prefix and suffix which are needed to be counted as individuals.

+-----------------------+-------+
|   Items   |   Values  |   QTY |   
+-----------------------+-------+
|   A-22    |   50      |   2   |
+-----------------------+-------+
|   B-11    |   20      |   1   |
+-----------------------+-------+
|   C-33    |   70      |   1   |
+-----------------------+-------+
|   D-54    |   40      |   1   |
+-----------------------+-------+
|   11      |   30      |   1   |
+-----------------------+-------+
|   22      |   10      |   1   |
+-----------------------+-------+
|   22-X    |   60      |   2   |
+-----------------------+-------+

It would save a lot of time and effort for me. Please help and thanks in advance.

Upvotes: 1

Views: 345

Answers (2)

player0
player0

Reputation: 1

={"QTY"; ARRAYFORMULA(IFERROR(VLOOKUP(TO_TEXT(A2:A),
  QUERY(TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, D2:D), ", ")),
  "select Col1,count(Col1) group by Col1 label count(Col1)''", 0), 2, 0)))}

0

Upvotes: 0

player0
player0

Reputation: 1

=SUM(ARRAYFORMULA(IFERROR(VLOOKUP(TRANSPOSE(SPLIT(D2, ", ")), A:B, 2, 0))))

0

Upvotes: 1

Related Questions