If the data sheet is named "Sheet1", and cell A1 has a valid Acc code, then yes... Note that you
should have a list of unique values in column A of Sheet2, not simply a copied list from Sheet1.

HTH,
Bernie
MS Excel MVP


"CP" wrote in message
...
Ok I think I am going to have to sort my data properley - remove merged
sections and headers as it still doesnt work but can I just ask the following

DataSheet!$A:$A = the whole of column A in the orignal data
$A2 = the matching section on the new sheet to match the original data
DataSheet!B:B = the source of data, ie the values to place on the new sheet

There fore after copying all my Acc numbers into Column A sheet2 I would
place this in the first row of the summary sheet in column B

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


"Bernie Deitrick" wrote:

Ooops. To copy across correctly, you need

=SUMIF(DataSheet!$A:$A,$A2,DataSheet!B:B)

Sorry about that....

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Put a list of your Acc codes down column A of your summany sheet, and use a formula like

=SUMIF(DataSheet!A:A,$A2,DataSheet!B:B)

and copy down and accross to make a table.

HTH,
Bernie
MS Excel MVP


"CP" wrote in message
...
Ok that seemed to take me in the right direction but then led me to further
problems. Good insite into pivot tables though. When I made the pivot table
it seemed only to want to take the first M value and wanted to ignore the
rest, I was maybe thinking there would be a vlookup to do this, another
problem is that there is other unorganised data in the table/columns merged
and such (this is data I get from elswhere). I was hoping to import the data
into sheet 1 each month and the other sheets pick out the right data -
considering the input data does not change in columns.??

"Bernie Deitrick" wrote:

CP,

For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as
data
fields.

For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type
column
to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster
waiting
to happen.

HTH,
Bernie
MS Excel MVP


"CP" wrote in message
...
Ok received alot of help here before hoping you can help again.

Table below showing my data on sheet1
Acc M1 M2 M3 Type
406 100 100 0 R
406 300 200 50 G
406 0 0 10 H
506 30 800 0 R
506 0 0 300 G

Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming
this will done on another sheet)
So you would get -

406 400 300 60
506 30 800 300

Secondly (on again another sheet the total) I need to move all the "R" rows
to another sheet - I would then do the same for H,G. So the R sheet would
become

406 100 100 0
506 30 800 0

I do have an understanding of naming rows if that would ease things.
I hope this makes sense. Thanks in advance for any help