asked on

excel macro to sum values

I have data in excel sheet1 like this

ITEMNO	DESCRIPTION	PERSON	QTY	RATE	VALUE
123	descr1  	per1	10	10	100
123	descr1  	per2	20	11	220
123	descr1  	per3	30	12	360
234	descr2  	per1	10	9	90
234	descr2  	per2	10	8	80
345	descr3  	per3	20	7	140
345	descr3  	per4	10	6	60

Open in new window

And I need the sum of values in sheet2 like this

ITEMNO	DESCRIPTION	TOT VAL	
123	descr1  	680	
234	descr2  	170	
345	descr3  	200

Open in new window

I need a macro to do this summation based on ITEMNO
sample.xls

nutsch

Why do you think you need a macro. Wouldn't a pivot table provide you the information you need? See attached file for pivot sample.
sample.xls

karunamoorthy

ASKER

it would be better to have a macro for my question pl

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Or you can use the SUMIF function, in sheet2 column C

=SUMIF(Sheet1!$A:$A,$A1,Sheet1!$F:$F)

Where A1 contains the ITEMNO and column B contains the Item Description, although not required for above.

Copy this down to required rows and A1 will adjust to new row.

Thanks
Rob H

karunamoorthy

ASKER

>>krishnakrkc,
The macro given by you works great! Could you pl give explanation or comment how it works, I have very little knowledge regarding excel macro.

krishnakrkc

Hi

See Patrick's article on Dictionary object.

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Kris

karunamoorthy

ASKER

Thank you very much