Sum and Vlookup

Microsoft Excel 2003 (full product)
April 5, 2010 at 14:20:36
Specs: Windows XP
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 $25

I 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!

See More: Sum and Vlookup

Report •

April 5, 2010 at 15:52:41
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:

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.

Report •

April 5, 2010 at 16:18:06
It's such a beatiful thing. Thank you for sharing this. I knew it looked something like this!

Report •

Related Solutions

Ask Question