+ Reply to Thread
Results 1 to 17 of 17

How do I use Sumproduct across 3 worksheets

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Question How do I use Sumproduct across 3 worksheets

    I realize this question has been asked before. I spent time looking at previous solutions but I cannot get it to work for my spreadsheet. I want to calculate weighted averages by using SUMPRODUCT or any other method. I have the formula in K3 of the attached spreadsheet it is like this
    Please Login or Register  to view this content.
    I want to use the same formula for the two other worksheets I have, but I am not getting it right. It has to be possible right?

    The aim is to have something like this SUMPRODUCT(L6:L18, M6:M18) sheet 2! (M5:M15,N5:N15) sheet3!(O2:O20, P(02:20). <----this is gibberish, but hopefully you get my point

    and then have just one value as the weighted average

    Thank you very much, I look forward to hearing from you.
    Attached Files Attached Files
    Last edited by spamunch; 01-19-2011 at 02:26 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How do I use Sumproduct across 3 worksheets

    It's only 3 sheets, why not just add them up, then divide?

    =(SUMPRODUCT(L6:L18, M6:M18) + SUMPRODUCT(lamey!L6:L18, lamey!M6:M18) + SUMPRODUCT('repo man'!L6:L18, 'repo man'!M6:M18)) / I2
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    Please Login or Register  to view this content.

    Thank you for your help JBeaucaire it worked perfectly. I have one more question when I2=0, I get a #DIV/0! how do I put a condition to remove that and have it blank

    2) How do I put a statement in the formula so it would ignore items in M6:M18, (lamey!M6:M18, and 'repo man'!M6:M18) which are empty or contain text?

    Thank you very much once again.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How do I use Sumproduct across 3 worksheets

    1) In the formula put this in place of the / I2 reference:

    / IF(I2>0, I2, 1)

    2) Your sample doesn't demonstrate any of that.

  5. #5
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    Quote Originally Posted by JBeaucaire View Post
    1) In the formula put this in place of the / I2 reference:

    / IF(I2>0, I2, 1)
    .

    Wow how do you know these things. It worked. Thank you

    Quote Originally Posted by JBeaucaire View Post
    2) Your sample doesn't demonstrate any of that.
    I am going to format my sample sheet to reflect the 0 variables and then I'll upload it. Please stay with me. i'll be back in 5 minutes

  6. #6
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    Please find the sheet attached. The first sheet has 3 missing rows in column M, second Sheet has none, and the third sheet has 2 missing . This is because I put If error conditions on those columns (in my real data), and if there was going to an error it would stay blank,

    My SUMPRODUCT formula apparently thinks it is a zero and it brings my weighting average down.

    Thank you very much!
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How do I use Sumproduct across 3 worksheets

    Very large fonts.
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How do I use Sumproduct across 3 worksheets

    I entered the equation (see below) into K3 and got 89.55.. Is this correct?

    =(SUMPRODUCT($L$6:$L18, $M$6:$M$18) + SUMPRODUCT(lamey!$L$6:$L$18, lamey!$M$6:$M$18) + SUMPRODUCT('repo man'!$L$6:$L$18, 'repo man'!$M$6:$M$18)) /(IF(I2>0,I2,1))

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I use Sumproduct across 3 worksheets

    @spamunch,

    You state you have formulae in your real file in Column M and that you're returning "blank" but mention 0 variables and lowering of results.
    I wonder then if perhaps you're using a 0 in your handler ?
    It's not clear in the sample which ranges you should be using for each sheet and you don't outline expected results so it's hard for anyone to test / verify


    Re: the #DIV/0! error - it would make a great deal more sense to test I2 prior to calculating the SUMPRODUCT:

    Please Login or Register  to view this content.
    else you're open to needlessly calculating the SUMPRODUCT (which may or may not become expensive over time)
    Last edited by DonkeyOte; 01-19-2011 at 06:01 AM. Reason: reworded

  10. #10
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    My apologies for not being clear at first. The formula I am using for the weighted average is in cell k3 of the first worksheet

    Please Login or Register  to view this content.
    Ranges are (L6:L18, M6:M18) on all worksheets ( sum products were performed on all) and divided by I2 (first worksheet). the above formula was placed in K3(first worksheet)

    When all the ranges have values then the weighted average formula returns 167.256

    However if you delete lets say M6 on the first worksheet the formula returns 162.21
    which means it treated M6 as a zero which then brings down the weighted average and makes it incorrect.

    What I want is for the formula to recalculate but disregard blank cells and don't treat them as 0. I uploaded a new much improved worksheet.

    Thank you .
    Attached Files Attached Files

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I use Sumproduct across 3 worksheets

    I confess I don't follow the logic behind the question given you're not conducting a weighted average.

    Presently you're summing the 3 SUMPRODUCTs and dividing that by I2.
    If M6 is 0 this simply means that particular SUMPRODUCT will generate a lower total - ie the numerator is lower - the divisor is unaffected.
    If you ignore M6 or treat as 0 the numerator will remain the same.

  12. #12
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    It is a weighted average calculation. I2= my denominator in my real file. It is a summation.

    Look at it this way if I have an excel spreadsheet, range b6:b10 with 4's in all of them, and I want to take an average. 4 4 4 4 =4, if i use range b6:b11, average of 4 4 4 4 0, is still 4. It disregarded the blank

    I would like that to put that condition in my SUMPRODUCT condition. Just like this other poster wanted, but I can't get it to work for me.

    http://www.excelforum.com/excel-work...-text-and.html

    Thanks
    Last edited by spamunch; 01-19-2011 at 10:10 AM. Reason: reworded

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I use Sumproduct across 3 worksheets

    Before this goes off track - the only thing that will affect your "weighted" average is the denominator, I2.

    To be clear:

    4 4 4 4 0

    will average 4 if you adjust the denominator to be 4 rather than 5

    If you include or exclude 0 the numerator does not change does it ? It's 16 either way.

    In your calculation the SUMPRODUCTs are your numerator whether you include 0 or exclude is of no consequence - I2 is the critical value.

    You state in your real file it's a summation whereas in your sample it's a constant. You will need to outline the specifics of how I2 is calculated.

  14. #14
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    Okay i changed I2 in the dummy fire to be I2
    Please Login or Register  to view this content.
    I uploaded the file again,

    Thanks
    Attached Files Attached Files
    Last edited by spamunch; 01-19-2011 at 11:06 AM. Reason: fixed error in code, uploaded worksheet

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I use Sumproduct across 3 worksheets

    OK so that makes more sense - in reality all you need do is change your SUMs to SUMIFs based on M range > 0 so as to discount L where M is blank/0/non-number

    Please Login or Register  to view this content.
    at which point altering M6 will not affect result.

  16. #16
    Registered User
    Join Date
    01-11-2011
    Location
    cleveland ohio
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How do I use Sumproduct across 3 worksheets

    DonkeyOte you made me work hard for this one, but it worked. Thank you very much for sticking with it. Thanks JBeaucaire!

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I use Sumproduct across 3 worksheets

    Quote Originally Posted by spamunch View Post
    you made me work hard for this one
    I made you work hard ...

    Glad you've resolved.

+ 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