Look Up Problem Monthly and Yearly Totals

[John Luc]

Hi
I hope some one can help me with this
I have a spreadsheet with monthly work worksheets named Apr07, May07, June
07 for the months of the year all the way through to Mar 08
with the following columns

Date Customer Name Sales
Company A 100
Company B 200
Company C 200
Company B 200
Company A 200
Company A 200

What I would like to do is have a running total on the monthly sheet of how
much each company has paid us this month

Giving this layout Company A Total = 500
Company B Total = 400
Company C Total = 200

Then I wish to create a new worksheet called yearly totals and bring each
company monthly total from each company
giving me a total that company A, Company B, Company C has paid this year
so far.

Many thanks for your help

John

 

[Max]

One way to frame it up using sumif & indirect..

Assume source sheets named as: Apr07, May07, etc
with CustName/companies in col B, Sales in col C,
data from row2 down

In the summary sheet,
List the monthly sheetnames as text in B1 across, eg: Apr07, May07
(enter an apostrophe before typing in the text: Apr07)
List the companies in A2 down, eg: Company A, Company B, ...

The table will look like this:

CustName Apr07 May07
Company A 500 300
Company B 400 100
Company C 200 0
etc

Place in B2:
=IF(COUNTA($A2,B$1)<2,"",SUMIF(INDIRECT("'"&B$1&"'!B:B"),$A2,INDIRECT("'"&B$1&"'!C:C")))
Copy B2 across as far as required, fill down to populate the table

 

[John Luc]

Hi Max
Thanks for the quick response I think I understand what your saying
regarding the summary sheet layout.
But how do I total the different companies payments on the monthly sheet for
instance Apr 07.

Or are you saying transfer in company monthly total manually using your
formula on the summary sheet.
As you have no doubt guessed excel is not a strong point with me.
Best Regards
John

 

[Max]

.. But how do I total the different companies payments on the monthly

sheet for instance Apr 07.

The suggested summary sheet set up would actually auto-extract the details
under each month's col. Imo, this is better than doing it separately in each
month's sheet.

Try this quick sample which illustrates the earlier:
http://cjoint.com/?mqxEAW5tOP
Summarizing from monthly sheets.xls

The only key presumption made is that you have a ready list
of all your customers (the company list) to easily paste into A2 down

---

 

[John Luc]

Hi Max
Thanks a million for this, may I wish a happy xmas and new year as well.
Best Regards
John

 

[John Luc]

Hi Max
Please can I ask you another favour can you alter formula for me to reflect
the following please
CustName = Column C
Sales = Column D

Tried to this myself and failed.
Regards
John

 

[Max]

If your source monthly sheets are like so:

CustName = Column C
Sales = Column D

In Summ,
just replace the formula in B2 with:
=IF(COUNTA($A2,B$1)<2,"",SUMIF(INDIRECT("'"&B$1&"'!C:C"),$A2,INDIRECT("'"&B$1&"'!D")))
then copy B2 across/fill down to populate the summary table
That should do it for you.

--------
This part of the earlier formula which points to the CustName in col B:
INDIRECT("'"&B$1&"'!B:B")
we change: B:B to C:C (the new col for the CustName)

and this part which earlier points to the Sales in col C:
INDIRECT("'"&B$1&"'!C:C")
we change: C:C to D (the new col for the Sales)

---