+ Reply to Thread
Results 1 to 8 of 8

Faster calculation instead of sumifs with three criteria in Excel 2007

  1. #1
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,912

    Faster calculation instead of sumifs with three criteria in Excel 2007

    I am using Excel 2007. One sheet name is "data" & second sheet is "calculation"
    In "data" sheet i have total 1,00,000 rows data.
    In "calculation" sheet i have total 4000 rows to calculate (using sumifs formula)
    My problem is its works slow & taking to much time for calculation.

    In "data" sheet column "b" is invoice date, column "c" is code, column "n" is quantity, column "s" is series & column "ab" is month&date.
    In "calculation" sheet i have column "a" code, column "b" customer name & from column "e" to "as" i calculate total sumifs with criteria code, month&date & series
    I have attach sample file (with very less data).

    I require vb micro for faster calculation.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    Don't reference entire columns like this: =SUMIFS(DATA!$N:$N;DATA!$C:$C;$A4;DATA!$AB:$AB;E$1;DATA!$S:$S;$C$2)
    Instead you should do this: =SUMIFS(DATA!$N$2:$N$107;DATA!$C$2:$C$107;$A4;DATA!$AB$2:$AB$107;E$1;DATA!$S$2:$S$107;$C$2)
    The problem with this is that when you add rows you need to update the formulas.

    The solution is to use the Excel Table feature (under the Insert tab, to the left). One of the advantages with this is that the Table will expand automatically when new data is added below the last row, no need to update the formulas.
    In this sheet I converted your data to Excel Table and redid the formula in cell E3 to this: =SUMIFS(Table1[QTY],Table1[CODE],$A3,Table1[MONTH],E$1,Table1[PRICE],$C$2)

    It is very easy to change the formulas. You can use find and replace (Ctrl + h) or you can just select the part of the formula you want to change and then select the Table column by moving the cursor carefully the top of that column and click when you see the black arrow pointing down. Sometimes it will select the normal column but then you have gone a little to high, just try again.



    http://www.contextures.com/xlExcelTable01.html
    http://www.techrepublic.com/blog/10-things/10-reasons-to-use-excels-table-object/
    http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/
    Attached Files Attached Files

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    Here is a solution for you using a table for the data and a formula that uses table nomenclature.

    The formula in Calculation!E3. Drag across and down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The [[ ]:[ ]] form absolute references in a formula referencing a table.
    Attached Files Attached Files
    Last edited by newdoverman; 06-29-2014 at 08:13 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    @Jacc

    Looks like we had pretty much the same idea for the same reasons, with a different implementation procedures.

    I think you made a typo in the formula choosing Price instead of Series...no big deal

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Instead of dragging this formula, select the row from E3:AS3 then use Ctrl R to fill in the formula along the row. Then double click the fill handle to fill down.

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    Gotcha!

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,912

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    I have going as per your formula in our original file. But i am sorry to say that, calculation processor running slow.
    If those data run through vb micro with using create button in excel sheet, then calculation of data much faster.
    Look once again.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,912

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    "Bump" awaiting for reply

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

    Re: Faster calculation instead of sumifs with three criteria in Excel 2007

    Please Login or Register  to view this content.
    How come you know this?

    Did you have tried it?

    If so, please post the code, so other forummembers can use them also.
    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.

+ 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. Replies: 6
    Last Post: 04-24-2014, 08:41 AM
  2. Replies: 2
    Last Post: 01-10-2013, 03:06 PM
  3. Excel 2007 : SUMIFS Value error in Excel 2007
    By sofib09 in forum Excel General
    Replies: 10
    Last Post: 01-13-2011, 04:35 PM
  4. SUMIFS function in Excel 2007
    By andrewc in forum Excel General
    Replies: 2
    Last Post: 09-08-2009, 11:32 AM
  5. Replies: 2
    Last Post: 02-27-2009, 08:29 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