+ Reply to Thread
Results 1 to 5 of 5

*SOLVED* SUMPRODUCT Alternative?

  1. #1
    Registered User
    Join Date
    05-01-2014
    Location
    Waco, TX
    MS-Off Ver
    MS Office 365
    Posts
    30

    *SOLVED* SUMPRODUCT Alternative?

    I recently added a SUMPRODUCT formula to one of my larger spreadsheets. It has completely bogged it down. I was wondering if someone could help with possibly a better formula w/ less calc time. What I am trying to achieve in the example below is to return a unique value where a customer only has a sale type of gross. In the example I use a sumproduct formula to achieve this to return a 0, but when I apply this formula to 100K+ rows of cells it is killing my already large workbook. Thanks in advance for the help.
    Attached Files Attached Files
    Last edited by PC41; 11-20-2018 at 12:16 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: SUMPRODUCT Alternative?

    Try this in C3:

    =COUNTIFS(B:B,B3,A:A,"<>Gross")

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: SUMPRODUCT Alternative?

    If the data is sorted on Customer name, I'd also suggest skipping the repeated calculation:

    =IF(B3<>B2,COUNTIFS(B:B,B3,A:A,"<>Gross"),C2)
    Rory

  4. #4
    Registered User
    Join Date
    05-01-2014
    Location
    Waco, TX
    MS-Off Ver
    MS Office 365
    Posts
    30

    Re: *SOLVED* SUMPRODUCT Alternative?

    Thank you both! I believe both will work in a more efficient way.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: *SOLVED* SUMPRODUCT Alternative?

    You're welcome. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Alternative to SUMPRODUCT
    By Prairie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-23-2017, 12:49 PM
  2. [SOLVED] Alternative to sumproduct
    By Questionz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-05-2017, 11:34 PM
  3. Alternative to SUMPRODUCT
    By matt45 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-07-2016, 09:08 AM
  4. [SOLVED] Alternative to SUMPRODUCT
    By jiteshmehta2k1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-23-2015, 11:23 PM
  5. Sumproduct Alternative
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2014, 04:57 PM
  6. Alternative to SUMPRODUCT???
    By VTdude12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2011, 04:03 PM
  7. Sumproduct alternative
    By DKerr in forum Excel General
    Replies: 6
    Last Post: 01-09-2009, 11:25 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