Solved

# Excel sumup  different units

Posted on 2011-05-06
Medium Priority
361 Views
Hi Experts,

Attached file can excel  sumup the  total as     " 1240DZ + 100PC + 100 BOX"   ?

Regards
Stanley
TOTAL.xls
0
Question by:candychan611
• 5
• 3
• 3
• +1

LVL 10

Expert Comment

ID: 35704656
A pivot table will suit your needs although it will not format like your example.

Regards
Gav
TOTAL.xls
0

LVL 22

Expert Comment

ID: 35704670
=SUMIF(B1:B24;"DZ";A1:A24) & "DZ + " & SUMIF(B1:B24;"PC";A1:A24) & "PC + " & SUMIF(B1:B24;"BOX";A1:A24) & "BOX"
0

LVL 22

Expert Comment

ID: 35704673
or
=SUMIF(B1:B24,"DZ",A1:A24) & "DZ + " & SUMIF(B1:B24,"PC",A1:A24) & "PC + " & SUMIF(B1:B24,"BOX",A1:A24) & "BOX"
depending on you regional settings.
0

Author Comment

ID: 35704765
Nicobo,  very good thx.

But in case of  72DZ + 0PC + 0BOX , can it show as        72DZ    ?
0

LVL 10

Expert Comment

ID: 35704824
=IF(SUMIF(B1:B24,"DZ",A1:A24)>0,SUMIF(B1:B24,"DZ",A1:A24) & "DZ","") & IF(SUMIF(B1:B24,"PC",A1:A24)>0, " + " & SUMIF(B1:B24,"PC",A1:A24) & "PC","") & IF(SUMIF(B1:B24,"BOX",A1:A24)>0," + " & SUMIF(B1:B24,"BOX",A1:A24) & "BOX","")
0

LVL 22

Expert Comment

ID: 35704841
That would be:
=MID(IF(SUMIF(B1:B24,"DZ",A1:A24)>0," + " & SUMIF(B1:B24,"DZ",A1:A24) & "DZ","") & IF(SUMIF(B1:B24,"PC",A1:A24)>0," + " & SUMIF(B1:B24,"PC",A1:A24) & "PC","") & IF(SUMIF(B1:B24,"BOX",A1:A24)>0," + " & SUMIF(B1:B24,"BOX",A1:A24) & "BOX",""),4,1000)

The IF checks if the result > 0 and only shows a result if it is > 0. All results are prefixed with " + ". The MID function skips the trailing " + ".
0

LVL 10

Assisted Solution

gavsmith earned 320 total points
ID: 35704863
@Nicobo - very clever, I was just about to repost my formula that would tidy that up but your version is much cleaner:

My version (getting very messy, DO NOT USE, use Nicobo's solution - just posting it for an example)
=IF(SUMIF(B1:B24,"DZ",A1:A24)>0,SUMIF(B1:B24,"DZ",A1:A24) & "DZ","") & IF(SUMIF(B1:B24,"PC",A1:A24)>0, IF(SUMIF(B1:B24,"DZ",A1:A24)>0," + ","") & SUMIF(B1:B24,"PC",A1:A24) & "PC","") & IF(SUMIF(B1:B24,"BOX",A1:A24)>0,IF(OR(SUMIF(B1:B24,"DZ",A1:A24)>0,SUMIF(B1:B24,"PC",A1:A24)>0), " + ","") & SUMIF(B1:B24,"BOX",A1:A24) & "BOX","")
0

LVL 50

Expert Comment

ID: 35704875

=SUBSTITUTE(TEXT(SUMIF(B1:B24,"DZ",A1:A24),""" + ""0""DZ"";;")&TEXT(SUMIF(B1:B24,"PC",A1:A24),""" + ""0""PC"";;")&TEXT(SUMIF(B1:B24,"BOX",A1:A24),""" + ""0""BOX"";;")," + ","",1)

regards, barry
0

LVL 22

Expert Comment

ID: 35704925
@gavsmith, thanks for the compliment. Lucky for you there are only 3 types and not 10, otherwise you were still working on your formula :-)

@barryhoudini, yes, great escape there Houdini. Even more compact.
0

LVL 50

Accepted Solution

barry houdini earned 360 total points
ID: 35704947
....one more with a little less "punctuation"

=SUBSTITUTE(TEXT(SUMIF(B1:B24,"DZ",A1:A24)," + 0\DZ;;")&TEXT(SUMIF(B1:B24,"PC",A1:A24)," + 0\PC;;")&TEXT(SUMIF(B1:B24,"BOX",A1:A24)," + 0\BOX;;")," + ","",1)

regards, barry
0

LVL 22

Assisted Solution

Nico Bontenbal earned 320 total points
ID: 35704974
Shouldn't that be
0\D\Z
0\P\C
0\B\O\X
You want to escape every character, not only the first. Suppose DM was one of the options then \DM wouldn't work but \D\M would.
0

Author Comment

ID: 35705112
THANKS ALL EXPERT'S HELP !
0

LVL 50

Expert Comment

ID: 35705264
Hey Nicobo,

Yes, you're right - although my suggestion works for those "codes" it might be better to "escape" every characater in case that might be required for any different codes- i.e.

=SUBSTITUTE(TEXT(SUMIF(B1:B24,"DZ",A1:A24)," + 0\D\Z;;")&TEXT(SUMIF(B1:B24,"PC",A1:A24)," + 0\P\C;;")&TEXT(SUMIF(B1:B24,"BOX",A1:A24)," + 0\B\O\X;;")," + ","",1)

regards, barry

regards, barry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, youâ€¦
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll