+ Reply to Thread
Results 1 to 3 of 3

Sum from several worksheets...

  1. #1
    Registered User
    Join Date
    05-08-2006
    Posts
    1

    Sum from several worksheets...

    Hello

    I'm a totally newbie so please excuse me if this is a really stupid question. My extent of excel so far is really poor but as I've done a bit of Access programming before I've been landed with this project!!! Some help and advice would be really gratefully appreciated!

    I have to create a worksheet that records the amount of time staff spend on different projects. There is a worksheet for each member of staff where the time spent on a project is recorded on a weekly basis (row titles for project; column headings = w/c date). I need to create a new worksheet that totals the amount spent by ALL STAFF on each project. I assume it will need to search the 'project' column of each worksheet and sum the values alongside it for each project?

    I've started by creating a ValidContracts list so all entries are identical (I'm very proud that I managed to work out how to do this!!! sad I know!) but I just don't know what to do now. Is there some kind of If Then Sum function that would search the Project Name column of each worksheet and add up the adjacent values for each specific project?

    Hope this makes some sort of sense!!!

  2. #2
    Miguel Zapico
    Guest

    RE: Sum from several worksheets...

    Hi,

    You may want to take a look at the SUMIF function in the Excel help, that
    may fit your needs. Also, for the consolidating process the function
    INDIRECT may also help, as you can store the workbook/worksheet names in
    cells instead of hardcoding formulas.

    If you need help beyond the help file, you may post again with an example.

    Miguel.

    "saross" wrote:

    >
    > Hello
    >
    > I'm a totally newbie so please excuse me if this is a really stupid
    > question. My extent of excel so far is really poor but as I've done a
    > bit of Access programming before I've been landed with this project!!!
    > Some help and advice would be really gratefully appreciated!
    >
    > I have to create a worksheet that records the amount of time staff
    > spend on different projects. There is a worksheet for each member of
    > staff where the time spent on a project is recorded on a weekly basis
    > (row titles for project; column headings = w/c date). I need to create
    > a new worksheet that totals the amount spent by ALL STAFF on each
    > project. I assume it will need to search the 'project' column of each
    > worksheet and sum the values alongside it for each project?
    >
    > I've started by creating a ValidContracts list so all entries are
    > identical (I'm very proud that I managed to work out how to do this!!!
    > sad I know!) but I just don't know what to do now. Is there some kind
    > of If Then Sum function that would search the Project Name column of
    > each worksheet and add up the adjacent values for each specific
    > project?
    >
    > Hope this makes some sort of sense!!!
    >
    >
    > --
    > saross
    > ------------------------------------------------------------------------
    > saross's Profile: http://www.excelforum.com/member.php...o&userid=34233
    > View this thread: http://www.excelforum.com/showthread...hreadid=539870
    >
    >


  3. #3
    Domenic
    Guest

    Re: Sum from several worksheets...

    Assuming that for each sheet Column A contains the project name, and
    Column B contains the corresponding values, try the following...

    List the sheet names in a range of cells, let's say D2:D5.

    List the project names in another range of cells, let's say Column E,
    starting at E2.

    Then...

    F2, copied down:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$D$2:$D$5&"'!A2:A100"),E2,INDIRECT("'"&$D$
    2:$D$5&"'!B2:B100")))

    Hope this helps!

    In article <[email protected]>,
    saross <[email protected]> wrote:

    > Hello
    >
    > I'm a totally newbie so please excuse me if this is a really stupid
    > question. My extent of excel so far is really poor but as I've done a
    > bit of Access programming before I've been landed with this project!!!
    > Some help and advice would be really gratefully appreciated!
    >
    > I have to create a worksheet that records the amount of time staff
    > spend on different projects. There is a worksheet for each member of
    > staff where the time spent on a project is recorded on a weekly basis
    > (row titles for project; column headings = w/c date). I need to create
    > a new worksheet that totals the amount spent by ALL STAFF on each
    > project. I assume it will need to search the 'project' column of each
    > worksheet and sum the values alongside it for each project?
    >
    > I've started by creating a ValidContracts list so all entries are
    > identical (I'm very proud that I managed to work out how to do this!!!
    > sad I know!) but I just don't know what to do now. Is there some kind
    > of If Then Sum function that would search the Project Name column of
    > each worksheet and add up the adjacent values for each specific
    > project?
    >
    > Hope this makes some sort of sense!!!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1