Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: SUMPRODUCT formula making spreadsheet run REALLY SLOW!
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular zookeepertx's Avatar
    Join Date
    May 2011
    Location
    NE TX
    Posts
    452
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    I've added a new post with samples of the problem in tables, since I can't link to the workbook.

    Thanks for taking a look!

    Jenny
    Using Excel 2010

    I have CDO... It's LIKE Obsessive-Compulsive Disorder, but alphabetical like it SHOULD be!

  2. #12
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    583
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    Hi Jenny,

    Even though Sumproduct can be slow the main culprit is the volatile indirect for the month year dates test. Here's a summary of what you could use. Either hard code your criteria into the code or reference a cell with the criteria such as some of the B Column rows.

    With the C2 & D2 setup you can choose what year & month you would like to start from. The Complete tab sheet data has been converted to a table called "Database" so that the formulas will dynamically update as new rows are added or deleted. I didn't attempt the Who's on EDI sheet as I wasn't sure of its build, if you want to post that sheet I can add the C10 formula to my mockup for you.

    After the formulas spill I've made a Dropbox link to my mockup for reference, copy and paste your Complete data into my mockup and test if it suits;

    Tip when working with table formulas, paste the formula into the first cell, then highlight that including all cells in the row ie C4:N4, then click after the end of the formula in the edit bar and use CTRL+enter. Note if you paste a table formula and drag across the table referencing can/will change table fields.

    ABCDFLQRST
    1APPROVE DATEPO #DATEDATE2VALIDATIONON EDIIMPORT FLAGSPEC CHARBULKDATE5
    210/08/2017101000938911/08/20177/07/2017WRONG SUPPLIER SITEYESYESNONO19/06/2017
    34/08/2017101000938772/08/201715/08/2017IMPORT ORDERNOYES
    43/08/2017101000939993/08/20177/07/2017GLC NOT LOADEDYESNO
    54/08/2017101000936654/08/201715/08/2017DOMESTICNONO
    64/09/2017101000937775/10/20178/10/2017GLC NOT LOADEDYES
    7
    89/10/2017101000935558/10/20175/10/2017DOMESTICNONO
    92/11/2017101000935558/10/20175/10/2017Allocation

    Complete






    BCDEFGHIJKLMNOP
    1Criteria1YearMonth
    220177
    31/07/20171/08/2017Sep 17Oct 17Nov 17Dec 17Jan 18Feb 18Mar 18Apr 18May 18Jun 18Jul 18Total
    4Month #04111000000007
    5Allocation00001000000001
    6Not Yes/Blank/No Column L01010000000002
    7GLC Not Loaded01100000000002
    8Wrong Supplier or Domestic02010000000003

    Month End Totals



    Worksheet Formulas
    CellFormula
    P4=SUM(C4:N4)
    C3=DATE($C$2,$D$2,1)
    D3=EOMONTH(C3,0)+1
    C4=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0))
    C5=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[VALIDATION],$B$5)
    C6=SUMPRODUCT((Database[APPROVE DATE]>=C$3)*(Database[APPROVE DATE]<=EOMONTH(C$3,0))*(Database[IMPORT FLAG]="No")*(Database[ON EDI]="No"))
    C7=COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[VALIDATION],$B$7)
    C8=SUMPRODUCT((Database[APPROVE DATE]>=C$3)*(Database[APPROVE DATE]<=EOMONTH(C$3,0))*(--(Database[VALIDATION]="Wrong Supplier Site")+(--(Database[VALIDATION]="Domestic"))))




    https://www.dropbox.com/s/j5ftgz9jup...ckup.xlsx?dl=0

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #13
    Board Regular zookeepertx's Avatar
    Join Date
    May 2011
    Location
    NE TX
    Posts
    452
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    Quote Originally Posted by RasGhul View Post
    Even though Sumproduct can be slow the main culprit is the volatile indirect for the month year dates test. Here's a summary of what you could use. Either hard code your criteria into the code or reference a cell with the criteria such as some of the B Column rows.

    With the C2 & D2 setup you can choose what year & month you would like to start from. The Complete tab sheet data has been converted to a table called "Database" so that the formulas will dynamically update as new rows are added or deleted. I didn't attempt the Who's on EDI sheet as I wasn't sure of its build, if you want to post that sheet I can add the C10 formula to my mockup for you.

    After the formulas spill I've made a Dropbox link to my mockup for reference, copy and paste your Complete data into my mockup and test if it suits;

    Tip when working with table formulas, paste the formula into the first cell, then highlight that including all cells in the row ie C4:N4, then click after the end of the formula in the edit bar and use CTRL+enter. Note if you paste a table formula and drag across the table referencing can/will change table fields.

    https://www.dropbox.com/s/j5ftgz9jup...ckup.xlsx?dl=0
    Ooh! Very interesting! I haven't worked with databases before! How do you convert to a table with a name? And how does it become dynamic? Just by the fact of being officially a table?

    I've played around and adjusted 1 or 2 of the formulas just to tweak them and am getting it figured out, but I'll probably be back with a question or two.

    I can't even TELL you how thrilled I am to know about highlighting/CTRL+enter to keep the references correct! I've always just had to manually adjust them every time! (I'll bet I'm the only one around here that knows that now. I'm gonna keep it a secret and impress people later )

    I have to clock out in 6 minutes and I won't be in tomorrow, but I'll get back to this on Monday.

    Thank you SO much; you've made my day! Have a great weekend.

    Jenny
    Using Excel 2010

    I have CDO... It's LIKE Obsessive-Compulsive Disorder, but alphabetical like it SHOULD be!

  4. #14
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    583
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    You're welcome thanks for the feedback

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #15
    Board Regular zookeepertx's Avatar
    Join Date
    May 2011
    Location
    NE TX
    Posts
    452
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    Hello again!

    I didn't get back to this on Monday; they keep wanting me to do my regular duties before working on stuff like this. (They're no fun, LOL!)

    The only adjustment I've made is changing the last reference in each formula. Column B doesn't actually show the descriptors shown; there are other titles in there. So I just made the formulas refer to the text that the formula is truly looking for.


    Anyway, I've run into a couple of problems:

    When I add rows to the "Complete" tab, they don't seem to get incorporated into the named table. I haven't done anything with tables before, so I have no clue how to address this. Also, how do you convert the data into a table and name it? (I've tried Googling and it just frustrated me)
    ------------------------------------------------------------------------------------------
    Also - on the 3rd row of data on the "Month End Totals" tab:
    That one needs to count everything in column Q that is NOT "YES" and is not blank + anything that has "NO" in column L UNLESS that row has already been counted because of not having "YES" or blank in Q.
    I can't seem to get my head wrapped around that, no matter how long I look at it!
    Let me try to describe it better -


    Column A - date Column L Column Q explanation
    YES EDI This would count as 1 because of "EDI" in Q
    NO YES This would not be counted because Q says "YES" but would count as 1 because in L it says "NO"
    OKAY This would count as 1 because Q is not "YES" and is not blank
    NO This would not be counted in Q because it's blank but would count as 1 because L is "NO"
    NO MERCH This would count as 1 because Q is not "YES" and is not blank but would also count as 1 because L is "NO". However, it should only be counted once

    SO, my theory has been this:
    • Count all that are not "YES" in column Q
    • Subtract from that all that are blank in column Q

    ***This should leave us with everything in Q that has ANYTHING besides "YES" or is blank.

    • But here's where I get tripped up - I can get an array of where column L is "NO" and get a count, but I can't wrap my brain around NOT counting where L is "NO" but the row has already been counted because of the data in Q


    You've got the rest of this figured out so beautifully; hopefully this last part is possible as well.

    Thank you!

    Jenny
    Using Excel 2010

    I have CDO... It's LIKE Obsessive-Compulsive Disorder, but alphabetical like it SHOULD be!

  6. #16
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    583
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    Hello,

    I would make a sample copy of your workbook to test, note that all the formulas have Table references so the data on the Complete tab needs to be converted to an excel table;

    Click any cell in the data on the Complete tab and press CTRL+T, click ok for the prompt that appears my table has headers.
    By default, excel uses a dark color scheme, when you click on a cell in the table, the table tools tab appears at the top.
    In the table tools on the far left you can edit the table name, usually it starts as Table1 but I labelled it Database in my mock-up
    Click on the drop down for table styles and click clear to remove the table color scheme.

    When done correctly when you start to type data into the approve date (Column A), you'll notice that the table auto expands to include this new row. This is what makes tables dynamic and will incorporate new data when calculating the formulas. Here's a link on tables for reference.

    https://www.youtube.com/watch?v=VMScRbwDSXE

    Row number 3 solution;

    I played with the logic for the sumproduct not yes & not blank but countif no in EDI column and I just couldn't get it to count properly.

    So the easiest solution would be to run a helper column on the end of your complete data and copy and drag this formula;

    =IF([@[ON EDI]]="NO",1,IF(AND([@[IMPORT FLAG]]<>"",[@[IMPORT FLAG]]<>"yes"),1,""))

    So if(NO is in column L = 1, if false then if column Q is not yes or Not blank = 1, otherwise be blank). This test seems to work correctly then use the following to count the 1's from the helper test column.

    =COUNTIFS(Database[APPROVE DATE],">="&C$3,Database[APPROVE DATE],"<="&EOMONTH(C$3,0),Database[Helper],1)


    AFLQU
    1APPROVE DATEVALIDATIONON EDIIMPORT FLAGHelper
    210/08/2017WRONG SUPPLIER SITEYESYES
    34/08/2017IMPORT ORDERNOYES1
    43/08/2017GLC NOT LOADEDYESNO1
    54/08/2017DOMESTICNONO1
    64/09/2017GLC NOT LOADED
    7play1
    89/10/2017DOMESTICNOmerch1
    92/11/2017Allocation

    Complete




    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •