Product Qty- Bundles/Singles

[Stren]

I cannot Add The Singles and Bundles
For Example I want to Sum 1B/20 (1 Box and 20 pieces) + 5B/40 (5 Boxes and 40 Pieces)
What Formula can I Use to Solve This
1B/20 + 5B/40 - 2B/12
Output: 4B/48

[AliGW]

Welcome to the forum.

You should have included expected results in your workbook and I suspect there is more to it, but try this for starters:

=SUM(IFERROR(--LEFT($B4:$E4,FIND(" ",$B4:$E4)-1),0))&" Boxes/"&SUM(IFERROR(--MID($B4:$E4,FIND("/",$B4:$E4)+1,99),0))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F
4	Apple Juice	0	0	0	0 pc	0 Boxes/0
5	Iced Tea	29 Box/27	0	3 Boxes	26 Box/27	58 Boxes/54
6	Bottle	5	0	5	0	0 Boxes/0
7	Medicine Brand	5 Box/36	0	8	5Box/28	5 Boxes/64
8	Vitamin	7 Box/3	0	0	7 Box/3	14 Boxes/6
9	Wine	1	0	0	1	0 Boxes/0
10	Ballpen	2 Box/97	0	0	2 Box/97	4 Boxes/194
11	Pencil	31 Bot/41	0	0	31Bot/41	31 Boxes/82

Sheet: Sheet1

[JohnTopley]

I suggest you re-format data as shown attached as your method is not good practice.

Total using

=SUMPRODUCT(($H5:$N5)*($H$4:$N$4="B"))

=SUMPRODUCT(($I5:$O5)*($I$4:$O$4="P"))

Much simpler

[AliGW]

Thank you for the rep.

Glad to have helped.