Summing up a set of numbers against a list of specific branches

[RJK2012]

Good afternoon

I hope you can help with my query below….

I have one years worth of data split into months on 12 worktabs. For each month’s data, I have done a pivot table that gives me 2 columns of data.

Column A – List of branch numbers. There are over 11000 branches

Column B – The number of transaction errors they have.

Per month, I have a list of branches (approx 300), and I need to sum up the total number of transaction errors they have had for that month.

e.g. for April, I have 310 branches to analyse. What I need is one figure, which is the sum of the transaction errors for those 310 branches.

Is there a way to do this in a quick and simple fashion, The long way I am thining about will take hours.

Hope that makes sense..

RJK2012

[NBVC]

It would be better if you could provide a reduced size sample workbook... that shows how you have the data layed out...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[RJK2012]

Hi,

Thanks for your response. Please see attached excel spreadsheet. I hope it provides enough information. If not, please let me know and I will have another go.

There are 2 worksheets

Worksheet 1 is sample data for one month only
Worksheet 2 is the pivot table results I am thinking of using

I have a list of branch numbers that I need to analyse per month. Is there a way I can use this list of branch numbers, that varies per month to sum up the list of transaction errors, that are in column B of the Pivot Table worktab.

Thanks


RJK2012

[NBVC]

I am not sure I follow, but can you add the date column to the Pivot Table, then Group by Month to give a count of errors for each FAD per month?

[RJK2012]

Hi,

Apologies, I am probably not explaining correctly.

On the 'Pivot Table' worksheet, there are approximately 6000 rows of data. Column A is a list of branches that have had transactions for a particular month, (April 2011 in this case). Column B has the total number of transactions errors for that month, for that branch.

I have a list of about 300 branches for April that I need to analyse. For these 300 branches, I need to sum up the number of transaction errors and present a statistic that says for the month of April, we had 300 (out of a total of 6000) branches that had transaction errors. The total number of transaction errors for these 300 stores is 859.

The problem with the pivot table is that there is no simple way to select the 300 branches I need, as the list of branches has 6000 in it. To keep ticking and unticking the boxes in the pivot table would take a very long time.

Is there am easy way to do this?

Thanks for your help,

RJK2012

[NBVC]

In a summary sheet, you can list your 300 IDs.

Then use a formula like:

=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT("'"&C$1&"'!$A$5:$A$6000"),$A$2:$A$5,0))),INDIRECT("'"&C$1&"'!$B$5:$B$6000"))

copied down and across a table which shows the tab headers in say row 1... so formula looks for tab of same name and summarizes...