How do i copy data from sub catergories in 1 worksheet to another worksheet as main catergories

[Wayne Rogers]

I have managed to get through numerous macro/VBA issues in my new work book that i'm putting together, however i'm completely stumped on this one.

I have a gross profit report that shows all the sales data by sub catergories, but i need to be able to summarise the values in the main catergories in a different sheet.

I.e. the sub category numbers are in column A and their names are in column B and their relevant costs are in column F
1002 BB 31,239
1007 CC 109,903
2072 DD 78,563
8739 EE 29,765
etc etc there are roughly 18 - 22 rows of data depending on the stores sales

I'd like to be able to add the value (£) for groups 1002 and 2072 and put it in 1 cell and then add groups 1007 and 8739 into the cell below on a different tab called Summary

I hope I've explained it clearly ?

Many thanks

Wayne

 

[ParamRay]

.
.

I don't know what your worksheet containing the sales data is called. So, for simplicity, I've called it "Sales Data".

Unless I've misunderstood your question, you can avoid using VBA and simply use two worksheet formulas in your "Summary" worksheet, namely

=SUMIF('Sales Data'!A:A,"1002",'Sales Data'!F:F)+SUMIF('Sales Data'!A:A,"2072",'Sales Data'!F:F)

and

=SUMIF('Sales Data'!A:A,"1007",'Sales Data'!F:F)+SUMIF('Sales Data'!A:A,"8739",'Sales Data'!F:F)

 

[Wayne Rogers]

Thank you gpeacock that formula works a treat, and i guess if i wanted to increase the sub catergories i would just repeat the +SUMIF( ........

Once again thank you, thats one major headache avoided.

Regards

Wayne

 

[ParamRay]

Thank you gpeacock that formula works a treat, and i guess if i wanted to increase the sub catergories i would just repeat the +SUMIF( ........

Once again thank you, thats one major headache avoided.

Regards

Wayne

No probs. Yep, just add another +SUMIF(... like you said.

 

[Wayne Rogers]

Hi G Peacock,

The formula you gave me works fine if i enter it after the vba has been run on the sales data sheet, but if i open up the workbook with your formula already inputted and then run the code i get a #REF in the cell and the formula changes to

=SUMIF(GP!A:A,"CR030306",GP!#REF!)

Am i doing something wrong do you know ?