+ Reply to Thread
Results 1 to 8 of 8

Adding a clause to SUMPRODUCT

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Adding a clause to SUMPRODUCT

    I can't seem to correctly add in an additional clause to my sumproduct formula below. Trying to add Data2!$E$3:$E$39073="Active" as an additional clause however having issues. Any help is greatly appreciated!

    =SUMPRODUCT((Data2!$D$3:$D$39073=$A2)*(Data2!$F$1:$BJ$1=$A2),Data2!$F$3:$BJ$39073)
    Last edited by bradharris2; 10-19-2012 at 11:46 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,545

    Re: Adding a clause to SUMPRODUCT

    Maybe:

    =SUMPRODUCT((Data2!$D$3:$D$39073=$A2)*(Data2!$F$1:$BJ$1=$A2)*(Data2!$F$3:$BJ$39073)*(Data2!$E$3:$E$39073="Active"))

    Can't test it, no data.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding a clause to SUMPRODUCT

    Thanks TMS. Unfortunately this doesn't work as I get a #VALUE! error which is what I was getting before.

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Adding a clause to SUMPRODUCT

    How about uploading a small sample file bradharris2

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,545

    Re: Adding a clause to SUMPRODUCT

    Well, generally, in an Array formula (which is what SUMPRODUCT is), the ranges are usually the same size. But, I assumed that what you had worked, so I tagged on the additional criterion. Comes back as 0 in my test but then I don't have any data to test it with.

    Regards, TMS

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,583

    Re: Adding a clause to SUMPRODUCT

    On a blank worksheet Trevor's formula works and gives 0.

    Perhaps there's a problem with the data, not the formula?

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Adding a clause to SUMPRODUCT

    Hey All - It was a one cell data error. Thank you all for your help!
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,545

    Re: Adding a clause to SUMPRODUCT

    Yep, this works:

    =SUMPRODUCT((Data2!$D$3:$D$39073="Q3 2009")*(Data2!$F$1:$BJ$1="Q3 2009")*(Data2!$F$3:$BJ$39073)*(Data2!$E$3:$E$39073="Active"))


    But it looks like you have no data that meets the criteria. I changed some data to test it and it will pick up the values

    Regards, TMS

+ 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