EXCEL: How can I combine common text values in column A and SUM number values in column B?

Question

I have two columns: A & B. Column A has text values, many of which repeat. Columb B has numbers. Is there a macro/shortcut/process whereby I can automatically combine/concatenate the text values in column A and in doing so, sum the values in column B? Right now I'm having to do SUM function and define range in B of the numbers that should be added for all the common text values in A.

E.G.

COLUMN A | COLUMN B
pumpkin 15
pumpkin 7
pumpkin 42
pumpkin 14
orange 5
orange 4
orange 85

i want to end up with two rows that say:

COLUMN A | COLUMN B
pumpkin 78
orange 94

Answer 1

You need to use a pivot table. Just highlight the cells with the data making sure you include the coumn headings. Then click Data->Pivot Table & Pivot Chart.

A new dialogue box opens, all the default settings are fine so just click finish. Excel creates a new worksheet and you will see a big empty box on the left and a little box to the right with the column headings in.

Drag the column heading which represents the fruit to the lefthand vertical column of the big box and drag the heading which relates to the values into the big middle section of the big box.

Excel has now created a list of unique fruits and their total values. I hope that's what you wanted!

Answer 2

If you use the SUM function, you'll always have to define the range. If you use SUBTOTAL instead "=SUBTOTAL(9,B2:BXX)", you'll only sum up what you show on the screen. If you do an autofilter by column A to show only the common values in A whose column B data you want to SUBTOTAL, the only values in B that will add up will be those belonging to the same value in Column A. No need to sort.

Alternatively, you could use the Subtotals function. First select column A&B at the top, go to Data --> Sort, choose Ascending by Column A. Then go to Data --> Subtotals, then select "At each change in (Column A's name)", "Use function Sum", and "Add subtotal to (Column B's name).

You'll end up with
Pumpkin TOTAL 78
Orange TOTAL 94

Answer 3

Here's what my spreadsheet ended up looking like: there is most likely an easier way, I'm just stuck on the way that first came to mind.

A - - - - - - - - B - - - - - - C - - - - - - D - - - - - - E
Pumpkin - - - 15- - - - - Pumpkin - Pumpkin- -Pumpkin 78
Pumpkin - - - 7- - - - - -Orange- - - Orange- - Orange 94
Pumpkin - - - 42
Pumpkin - - - 14
Orange - - - - 5- - - -- - -Orange
Orange - - - - 4
Orange - - - - 85

Formula for Column C
=IF(COUNTIF($A$1:A1, A1)=1, A1, "")

Named the range for C1:C7 as Blanks
Named the range for D1:D7 as NoBlanks

Formula for Column D (This is an array formula, press Ctrl+Shift+Enter instead of just Enter)
=IF(ROW()-ROW(noblanks) + 1>ROWS(blanks) - COUNTBLANK(blanks), "", INDIRECT(ADDRESS(SMALL((IF(blanks <> "", ROW(blanks), ROW() + ROWS(blanks))), ROW() - ROW(noblanks) + 1), COLUMN(blanks), 4)))

Formula for Column E (This is also an array formula)
=IF(D1 = "Pumpkin", "Pumpkin" & " " & SUM(($A$1:$A$7 = "Pumpkin") * $B$1:$B$7), IF(D1 = "Orange", "Orange" & " " & SUM(($A$1:$A$7 = "Orange") * $B$1:$B$7), ""))

So, the results you are looking for are in Column E; an awkward, kludgy attempt at answering, but I couldn't quit trying.

Answer 4

It should sum currency values! Make sure you format the entire column to currency. The $ signs stay there and the sum will also have the $ sign.

Answer 5

Cols:
A B C
ace12ace57
deep16
ace45
bye37
good78
lip34
bye23

ace57 in col c comes about by putting in the formula =A1&SUMIF(A1:A7,"ace",B1:B7)

Answer 6

Correct me if I misunderstand you -- you are trying to combine numbers and text?

Add apples and oranges?

You lost me.

Answer 7

call me at (636)-486-4086 i will explain u online..

Answer 8

and all this time i though i was an excel expert...huh

Answer 9

i dont understand ur question