+ 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
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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 2007 Enterprise
    Posts
    728

    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... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,087

    Re: Alternative to SUMPRODUCT

    Comment withdrawn by me.
    Glenn



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

    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
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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. Alternative to SUMPRODUCT?
    By staeri@gmail.com in forum Excel General
    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