In sheet 1 I have a list of account codes in column A with budget values in column B: ColumnA Column B

123 $50

123 $35

124 $25

124 $25

124 $25I want to end up with a summary list on Sheet 2 that reads like this:

ColumnA Column B

123 $85

124 $75

What formula would I use to produce these totals?Thank you!

Start by creating a list of unique values on Sheet2. Data...Filter...Advanced Filter can help you do this.

Once you have this on Sheet2:

A 1 123 2 124 3 etc.You can put this in Sheet2!B1 and drag it down:

=SUMIF(Sheet1!$A$1:$A$5, A1, Sheet1!$B$1:$B$5)

This will lookup the value in Sheet2!A1 (123) in the range Sheet1!A1:A5 and sum the corresponding cells in Sheet1!B1:B5.

Make sure you use the $ as shown so that the lookup and sum ranges stay constant but the lookup cell increments each time.

It's such a beatiful thing. Thank you for sharing this. I knew it looked something like this!

Ask Your Question

Weekly Poll