Excel sumup different units

Hi Experts,

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

Regards
Stanley
TOTAL.xls
candychan611Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Regards
Gav
 TOTAL.xls
0
 
Nico BontenbalCommented:
=SUMIF(B1:B24;"DZ";A1:A24) & "DZ + " & SUMIF(B1:B24;"PC";A1:A24) & "PC + " & SUMIF(B1:B24;"BOX";A1:A24) & "BOX"
0
 
Nico BontenbalCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
candychan611Author Commented:
Nicobo,  very good thx.

But in case of  72DZ + 0PC + 0BOX , can it show as        72DZ    ?
0
 
gavsmithCommented:
=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
 
Nico BontenbalCommented:
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
 
gavsmithCommented:
@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
 
barry houdiniCommented:
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
 
Nico BontenbalCommented:
@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
 
barry houdiniCommented:
....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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Nico BontenbalCommented:
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
 
candychan611Author Commented:
THANKS ALL EXPERT'S HELP !
0
 
barry houdiniCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.