+ Reply to Thread
Results 1 to 9 of 9

SUMPRODUCT across worksheets

  1. #1
    Forum Contributor
    Join Date
    09-18-2008
    Location
    Scotland
    Posts
    156

    SUMPRODUCT across worksheets

    I'm trying to get SUMPRODUCT to work across worksheets but am getting #REF
    here is my formula:

    =SUMPRODUCT(('MP 2010'!B2:B1312>=K1)*('MP 2010'!B2:B1312<=K2))

    Am I doing something blatently stupid here?

    Cheers
    Last edited by guerillaexcel; 04-16-2010 at 09:37 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMPRODUCT across worksheets

    Am I doing something blatently stupid here?
    No.

    Sumproduct does not work in 3D.

    You'll need to do individual sums on each sheet and then do a 3D sum

  3. #3
    Forum Contributor
    Join Date
    09-18-2008
    Location
    Scotland
    Posts
    156

    Re: SUMPRODUCT across worksheets

    that’s a pity but it explains whats happening so thanks for your advice.

    Ok, my next question is that Im counting a search term that falls between two dates. AI2 and AJ2 are my date range, in this instance between 01 jan 2010 and 31 jan 2010 and my search term is "inst".

    I'd like to add a drop down box (validation) that lets me select February or March and will automatically change my results for that date range. Any ideas?
    =SUMPRODUCT(B2:B2000=AI2)*(B2:B2000<=AJ2)*(G2:G2000="inst"))

    Thanks again

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMPRODUCT across worksheets

    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.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  5. #5
    Forum Contributor
    Join Date
    09-18-2008
    Location
    Scotland
    Posts
    156

    Re: SUMPRODUCT across worksheets

    Yes of course, here is a sample spreadsheet with my data.
    I've updated my sample formula for the January feed

    =SUMPRODUCT(($B$2:$B$2000>=Q2)*($B$2:$B$2000<=R2)*($G$2:$G$2000="inst"))

    so to clarify, in I2, I'd like to be able to select February or March and have the figures update in L2, M2, and N2 respectively.

    Cheers
    Attached Files Attached Files

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUMPRODUCT across worksheets

    Your formulas can be simplified it the data validation refers to a date value instead of text. The Sumproduct can then check for the month and the year of the date value. Also, you don't need to hard-code the texts if you refer the values in L1 to N1 in the sumproduct.

    Effectively, you have only one formula, which you apply in L2 and copy right

    =SUMPRODUCT(--(MONTH($B$2:$B$2000)=MONTH($I$2)),--(YEAR($B$2:$B$2000)=YEAR($I$2)),--($G$2:$G$2000=L$1))

    see attached

    hth
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: SUMPRODUCT across worksheets

    Teylyn,
    Shouldn't it be possible to make sumproduct work in 3D by using named ranges? See attachment where i moved the "Term" column to Sheet2.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    09-18-2008
    Location
    Scotland
    Posts
    156

    Re: SUMPRODUCT across worksheets

    You are both very kind, thank you again for your help, either solution works just fine.
    Can I make one final request

    I also have a time column in column H and would like to return the time to complete for each search term. i.e in january "inst" took 40 hours, "chel" took 15 etc. I have attached the updated spreadsheet as an example.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    09-18-2008
    Location
    Scotland
    Posts
    156

    Re: SUMPRODUCT across worksheets

    I'll post a new thread as the question has changed, the original question has been answered, thanks guys!
    Last edited by guerillaexcel; 04-16-2010 at 09:37 AM. Reason: Solved

+ 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