+ Reply to Thread
Results 1 to 10 of 10

SUMIFS to SUMPRODUCT

  1. #1
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    SUMIFS to SUMPRODUCT

    Hi,

    I have been trying to get this statement into a SUMPRODUCT statement. I have googled it, tried the various syntax that people have come up with and all i get is errors!

    =SUMIFS(Data!$R$2:$R$7014,Data!$Q$2:$Q$7014,'League Table'!$D5,Data!$N$2:$N$7014,"Yes",Data!$J$2:$J$7014,"Y")

    Any help turning this into a sumproduct so it can work in 2003?!!!

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: SUMIFS to SUMPRODUCT

    Try..

    =SUMPRODUCT(--(Data!$Q$2:$Q$7014='League table'!$D5),--(Data!$N$2:$N$7014="Yes"),--(Data!$J$2:$J$7014="Y"),Data!$R$2:$R$7014)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: SUMIFS to SUMPRODUCT

    Try this:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS to SUMPRODUCT

    Maybe

    Please Login or Register  to view this content.
    HTH
    Regards, Jeff

  5. #5
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: SUMIFS to SUMPRODUCT

    Thanks for your help gents. Tried both, and get an #N/A result. That's the sort of syntax i had found, can't understand why the SUMIFS works but i get N/A# on this.

    D5 is a country name, i have tried substituting the cell reference for a hard coded "COUNTRY" and still no avail... this is doing my head in/

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS to SUMPRODUCT

    Can you post a mock sample workbook?

  7. #7
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: SUMIFS to SUMPRODUCT

    Next step would be to check out the data to make sure there's no type mismatches.

  8. #8
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: SUMIFS to SUMPRODUCT

    Thanks guys, can you elaborate on what you mean by a type mismatch?

    I tried Jeffreys suggestion, the workbook was large and complex, a lot of the data is from lookups or formulas that generate the YES, NO, Y markers on a table.

    I tried stripping the table down to simplify in order to post, converting formulas to values etc... And at some point this "fixed" the SUMPRODUCT as when i got back to my summary table the calculation had taken place...

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: SUMIFS to SUMPRODUCT

    Sumproduct will give that error if you have any #N/A! errors in the data...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  10. #10
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: SUMIFS to SUMPRODUCT

    And BOOM goes the dynamite....thanks you SOOO much - found ONE erroneous N/A+ value that my formulas in the underlying data hadn't accounted for. Removed that and got the SUMPRODUCT to work - thank you so much!

+ 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