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

Wayne Rogers

New Member
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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ParamRay

Well-known Member
.
.

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

New Member
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

Well-known Member
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

New Member
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 ?

Replies
6
Views
397
Replies
1
Views
269
Replies
1
Views
857
Replies
7
Views
362
Replies
1
Views
196

1,195,720
Messages
6,011,292
Members
441,599
Latest member
Jribas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back