?
Solved

Excel sumup  different units

Posted on 2011-05-06
13
Medium Priority
?
361 Views
Last Modified: 2012-05-11
Hi Experts,

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

Regards
Stanley
TOTAL.xls
0
Comment
Question by:candychan611
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:gavsmith
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

by:Nico Bontenbal
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

by:Nico Bontenbal
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:candychan611
ID: 35704765
Nicobo,  very good thx.

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

Expert Comment

by:gavsmith
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

by:Nico Bontenbal
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

by:gavsmith
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

by:barry houdini
ID: 35704875
How about this to shorten and avoid any trailing or leading " + "s?

=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

by:Nico Bontenbal
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

by:
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

by:Nico Bontenbal
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

by:candychan611
ID: 35705112
THANKS ALL EXPERT'S HELP !
0
 
LVL 50

Expert Comment

by:barry houdini
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question