Help needed with a calculation question

[CP]

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

 

[Bernie Deitrick]

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]

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]

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

 

[Bernie Deitrick]

Ooops. To copy across correctly, you need

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

Sorry about that....

HTH,
Bernie
MS Excel MVP

 

[CP]

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]

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