I have an Excel spreadsheet like below. Colors are selected from a set list in column A and have a measurement (>0) in column B. I would like to create formulas in the cells under "totals" that populates in both the color (in the order it appears) and the corresponding sum of the cells. I would only like to show colors that have values and note that the colors may appear in any order. Is there an appropriate function/combination to achieve this?


1 Answer 1


You can use a simple pivot table for this:

  1. Mark the table of the colors and values.
  2. Go to "Insert" tab and press the pivot table button.
  3. You will be asked to mark the table to analyze and to specify where will the output go. You can choose to put it in a new sheet or within the same sheet (as in your example).
  4. After pressing OK, you'll see the pivot table menu to your right. Drag the colors field to the "Row Labels" box, and the values field to the "Values" box.

And you're done. As a bonus the pivot table also sorts the list of colors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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