+ Reply to Thread
Results 1 to 10 of 10

SUMPRODUCT alternatives

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    Exeter
    MS-Off Ver
    Excel 2007
    Posts
    7

    SUMPRODUCT alternatives

    Hi All,

    I recently had the problem of using sumif function across a horizontal and vertical plane at the same time. I have used a sumproduct function to solve my difficulties and am getting the results I want. However the data size this formula is covering is large and the sumproduct function is taking up too much of my work PC processing power (have requested an upgrade - denied).

    Please can you help me come up with an alternative formula that will:

    a) Get the right results (sum the right cells)
    b) Take less power to calculate
    c) Be very easy to drag across a large number of cells

    I have atached a sample data set with the formula in. I have removed the $ signs for ease but would reinsert these so the formula can be dragged around. The formula is in the highlighted cell.

    Many Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: SUMPRODUCT alternatives

    You can try this array formula but I don't think it is a better alternative to a SUMPRODUCT however, the PivotTable is.

    =SUM(IF($A$4:$A$12=$A16,$B$2:$G$2=B16)*$B$4:$G$12)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    02-17-2014
    Location
    Exeter
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SUMPRODUCT alternatives

    Thanks, I will try the array formula.

    I can't get the pivot table to give me what i want - i will try using one again though

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT alternatives

    Try this in B17 Filled right/down

    =SUMIF($A$4:$A$12,$A$16,INDEX($B$4:$G$12,0,MATCH(B$16,$B$2:$G$2,0)+($A17-1)))

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: SUMPRODUCT alternatives

    @Jonmo1, Very nice combination of SUMIF/INDEX/MATCH! Didn't think of it.

  6. #6
    Registered User
    Join Date
    02-17-2014
    Location
    Exeter
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SUMPRODUCT alternatives

    Hi Jonmo1,

    Thanks very much for your help, however my sample dataset was slightly too basic. The numbers do not run 1,2,3 in the real data which causes problems with the final part of your index calculation ($A17-1). I don't know enough about the index formula to work out an alternative. The real data set is shown below, can you still help?

    Jan-14 Feb-14 Mar-14 Apr-14 May-14
    3C22 730000 #REF!
    3C22 730020
    3C22 730080
    3C22 730081
    3C22 730200
    3C22 378151
    3C22 730001
    3C90 730000
    3C90 730020
    3C90 730080
    3C90 730081
    3C90 730200
    3C90 378151
    3C90 730001


    Many Thanks

  7. #7
    Registered User
    Join Date
    02-17-2014
    Location
    Exeter
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SUMPRODUCT alternatives

    However the dates dont look quite like that - they are spread out to the right as in the sample.

    Thanks

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT alternatives

    Well, we can only work with what you show us.

    Can you post a more realistic sample book?


    What you just posted makes it look simpler.
    Try just removing the +($A17-1) part

  9. #9
    Registered User
    Join Date
    02-17-2014
    Location
    Exeter
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: SUMPRODUCT alternatives

    I have attached a more realistic workbook. New data is in tab 2.

    The removal of the final section doesn't take into consideration the GL code line - it looks up 730000 each time.

    Thanks again for you help
    Attached Files Attached Files

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT alternatives

    Are the numbers in Row 3 always going to be the same repeated accross each date?
    i.e., will B3:F3 be exactly the same as G3:K3, and exactly the same as L3:P3

    If so, try this in C23

    =SUMIF($A$4:$A$15,$A23,INDEX($B$4:$P$15,0,MATCH(C$22,$B$2:$P$2,0)+(MATCH($B23,$B$3:$F$3,0)-1)))

+ 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. Lookup alternatives
    By _Lewis in forum Excel General
    Replies: 7
    Last Post: 04-12-2011, 04:32 AM
  2. Sumproduct alternatives
    By kossdust in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-15-2007, 06:05 PM
  3. What are the alternatives ???
    By christopherp in forum Excel General
    Replies: 4
    Last Post: 03-19-2006, 10:39 AM
  4. Sumproduct doesn't work with columns... alternatives?
    By qwopzxnm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2006, 06:40 PM
  5. Alternatives for Sumproduct?
    By Martin Los in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2005, 06:05 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