up vote 1 down vote favorite
1

I have a question on how to find the maximum total freight weight for the truck numbers and colors specified below using excel spreadsheet?

FYI just by looking at the numbers and color of the trucks, it is easy to identify that the green trucks have more freight weight of 300+300= 600Kg. This number is more than the sum of the freight weight compared to the other colored trucks. I have a large excel data with similar concept and would like to find an easier way of finding the maximum total weight of a specific color truck.

Please help

Truck Number    Truck Color    Freight Weight (Kg)
1                 Yellow       100
2                 Yellow       300
3                 Blue         200
4                 Orange       100
5                 Red          400
6                 Red          100
7                 White        150
8                 Green        300
9                 Green        300
10                Brown        100
share|improve this question
  • 1
    You can use a Pivot Table. Exactly how you want the results laid out is not clear, but explore the Pivot Table options until you get what you want. – Ron Rosenfeld Jul 30 '16 at 20:24
up vote 1 down vote accepted

PivotTable to summarize data

A PivotTable that summarizes the sum of freight weight by truck color allows you to find the maximum weight for a large set of data.

Using the example provided, build a pivot table with Truck Color in rows, and summary values as Sum of Freight Weight.

build pivot table

Modify the table by adding the formula =MAX(B4:B10) in cell B11. Add conditional formatting to the table to highlight the row or rows where column B matches the maximum value in $B$11.

pivot table

Expand the idea by adding Truck Number in columns of the pivot table. Enter the formula =MAX(L5:L11) in cell L12, and again add conditional formatting to highlight the row or rows where column L matches the maximum value in $L$12.

pivot table with columns

share|improve this answer
up vote 0 down vote

I think you can apply filter for color and then use MAX function

share|improve this answer
  • Welcome to Super User! This is really a comment and not an answer to the original question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. Please read Why do I need 50 reputation to comment? What can I do instead? – DavidPostill♦ Jul 30 '16 at 17:08
  • The question asks "how", and this is more of a suggestion of a direction to investigate. Can you expand your answer to illustrate the solution? – fixer1234 Jul 30 '16 at 17:23
  • In the category name in this case truck color you select filter, then you get a button to show drop down menu in the cell. In the menu you select those colors you want to keep. After filter is applied you enter one cell with =MAX list of functions will popup and you select MAX then you select all wanted cells and it will show you highest value. If you want I can post some screenshots. – Dušan Jul 30 '16 at 17:59
  • @Dušan, adding the description in your comment and some screenshots to your answer would be great (turn it into a real answer). As a newcomer, you may not have enough rep to post pictures, but I'll be happy to help with that. Just post links to any images at a sharing site, like imgur.com. "Address" a comment to me with @, like I did here, and I'll get an alert. – fixer1234 Jul 30 '16 at 23:22

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.