+ Reply to Thread
Results 1 to 8 of 8

Alternative to SUMPRODUCT

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    15

    Alternative to SUMPRODUCT

    I was using SUMPRODUCT function in my excel. The formula gives me the result but it is impacting performance as the formula is used in a 70x210 matrix.

    Formula used is as follows:


    SUMPRODUCT( ($A14='Merged Tab with Home Country'!$E$2:$E$7243) * ('Merged Tab with Home Country'!$J$2:$HK$7243>= 10) * ('Merged Tab with Home Country'!$J$2:$HK$7243 <= 100) *(W$10= 'Merged Tab with Home Country'!$J$1:$HK$1) * ($A14 <> 'Merged Tab with Home Country'!$J$1:$HK$1) * ('Merged Tab with Home Country'!$D$2:$D$7243 = TRUE) * ('Merged Tab with Home Country'!$C$2:$C$7243 > 1))


    Is there some faster alternative?

    Thanks in advance for your help.
    Regards,

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Alternative to SUMPRODUCT

    Can you post a small sample file as it is easier to work with rather than just a formula alone. Thank you.

  3. #3
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Alternative to SUMPRODUCT

    Can sumifs() solve your problem. It can handle more than one criteria

    Thanks
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  4. #4
    Registered User
    Join Date
    07-15-2015
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    15

    Re: Alternative to SUMPRODUCT

    Hi John,

    Enclosing the file.

    Regards,
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to SUMPRODUCT

    Comment withdrawn by me.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Alternative to SUMPRODUCT

    Use a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Alternative to SUMPRODUCT

    Thank you for your file.

    As was suggested by Mahju the usual alternative to SUMPRODUCT is SUMIF(S). However in your case you are not summing a numerical range but a count of conditions so SUMIFS will not work as it expects to sum a numeric range.

    The only option I can see is to change the TRUE condition in column D id in "Raw Data" to 1 (=TRUE) and blank/0 for FALSE: this would allow you SUM this column.

    The SUMPRODUCT formula

    =SUMPRODUCT( ($A7='Raw Data'!$E$2:$E$282) * ('Raw Data'!$J$2:$HK$282> 100) * ('Raw Data'!$J$2:$HK$282 <= 2000) *(C$4= 'Raw Data'!$J$1:$HK$1) * ($A7 <> 'Raw Data'!$J$1:$HK$1) * ('Raw Data'!$D$2:$D$282 = TRUE) * ('Raw Data'!$C$2:$C$282 > 1))

    translates to ..

    =SUMIFS('Raw Data'!$D$2:$D$282,'Raw Data'!$E$2:$E$282,"="&$A7,'Raw Data'!$J$2:$HK$282,">100",'Raw Data'!$J$2:$HK$282,"<= 2000",'Raw Data'!$J$1:$HK$1,"="&C$4,'Raw Data'!$J$1:$HK$1,"<>"&$A7,'Raw Data'!$C$2:$C$282," > 1")



    assuming the change to column D.

  8. #8
    Registered User
    Join Date
    07-15-2015
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    15

    Re: Alternative to SUMPRODUCT

    Thanks John, but the sumif did not work for me. I believe we cannot have a matrix 'Raw Data'!$J$2:$HK$282' in the forumala condition.
    Anyway thanks for taking the time out.

    I was able to find another solution.

    =IF($A13<>W$10,COUNTIFS(INDIRECT(W$12),">=10",INDIRECT(W$12),"<=100",'Merged Tab with Home Country'!$E:$E,$A13,'Merged Tab with Home Country'!$D:$D,TRUE,'Merged Tab with Home Country'!$C:$C,">1" ), 0)

    And W12 cell has the following:
    =SUBSTITUTE(ADDRESS(1,MATCH(W$10,'Merged Tab with Home Country'!$A$1:$HK$1,0), 4, 1, "Merged Tab with Home Country") &":"& ADDRESS(1,MATCH(W$10,'Merged Tab with Home Country'!$A$1:$HK$1,0), 4, 1), 1, "")
    Output of this formula gives me: 'Merged Tab with Home Country'!K:K
    So the column K is the column which matches my country and I can then apply the first formula to it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sumproduct Alternative
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 04:57 PM
  2. Alternative to SUMPRODUCT???
    By VTdude12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2011, 04:03 PM
  3. Alternative to SUMPRODUCT?
    By cjrhoads in forum Excel General
    Replies: 26
    Last Post: 05-14-2010, 03:32 PM
  4. Sumproduct alternative
    By DKerr in forum Excel General
    Replies: 6
    Last Post: 01-09-2009, 11:25 AM
  5. alternative from Coutif to Sumproduct
    By djfatboyfats in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-04-2008, 09:20 AM
  6. Sumproduct - Alternative
    By Harlequin in forum Excel General
    Replies: 2
    Last Post: 08-09-2007, 12:52 PM
  7. Replies: 1
    Last Post: 06-09-2006, 07:10 AM

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