Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi folks, could someone tell me if it's possible to use a function to get XL to take all the columns in a sheet that have a date within a set range (eg 2010, or 1/4/09-31/3/10) in a specified cell at the top, and total up all the entries in a set row within that range?

    To give a bit more detail - I'm creating a spreadsheet for someone who sells a range of about three dozen items, listed in column A. Across the top are the invoice numbers and dates, then how many of each item is given in the cell where they cross. I need to get annual totals for specified rows (individually) of how many of that item have been sold in the specified date range (Jan-Dec annually in most cases, Apr-Mar in one).

    I'd like to set it up so at the end of each year, the owner can just copy over the totals columns (on a separate sheet in the book), put the new year at the top and have the function automatically from that year, but I'm not sure if I might be asking too much of the functions! If so I'm happy to set up a VBA solution (which I would also need help with - I'm terribly rusty!) and have a button for him to click to update the totals sheet automatically, or whatever ...

    PS Hi everyone who remembers me! Long time no visit, I know, I don't work in IT proper any more, just get occasional projects like this ...! Boy, have you redecorated since I was last here ...!!
    Beryl M


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are the "invoice numbers" text or numbers?

    If they are text you can use a sumproduct to total only the cells in the column with numbers in that row or if there is a pattern that can be used to only look at particular columns.

    If that is not enough info, Can you post an example file so we can detail a formula?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Um - I'm not sure exactly what you're asking, here - there is a text row containing the invoice number (sometimes the recipient, they're not always numbered), then a row containing the date which is in a date format, then a row for each item and a number in the appropriate cell to say that this invoice was for one of these, two of those and one of the other ...

    There's nothing confidential about it, so here's a screenshot - hope it's not too big.

    By the way, what happened to our avatars, sigs, etc?
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry, forgot the attachment!
    Attached Images Attached Images
    Beryl M


  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    PS The date does include the year, it just doesn't display it.
    Beryl M


  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample WORKBOOK (not a screenshot) and elaborate on what you want to be able to calculate. From your description I had imagined some columns with dates and some with invoice numbers and only adding the ones with dates, but your range seems to have dates so I am unclear what you want / need...

    Steve

Posting Permissions

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