• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS Clean Up

S

Sara

Member
Hi there


I have a really slow workbook that has 88 sumifs formulas plus 22 array formulas.

While I was pretty pleased that I'd figured out how to write them, I'm pretty sure that there must be a way of rewriting them to make them faster.

This is a weekly task for me and currently the workbook is taking up to 5 minutes to calculate.


The sumifs are along these lines:

=ROUND(

SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800000")

+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800500")

+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800000")

+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800500")

+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800000")

+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800500")

+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800100")

+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800215")

+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800100")

+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800215")

+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800100")

+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800215"),2)


While my arrays are along these lines:

{=SUM(IF((B2=Dept_Id)*("Earnings"=Line_Descr)*("800000"=Account),1/COUNTIFS(Dept_Id,B2,Emplid,Emplid,Line_Descr,"Earnings",Account,"800000")),0)

+SUM(IF((B2=Dept_Id)*("Earnings"=Line_Descr)*("800500"=Account),1/COUNTIFS(Dept_Id,B2,Emplid,Emplid,Line_Descr,"Earnings",Account,"800500")),0)

+SUM(IF((B2=Dept_Id)*("Earnings"=Line_Descr)*("800100"=Account),1/COUNTIFS(Dept_Id,B2,Emplid,Emplid,Line_Descr,"Earnings",Account,"800100")),0)

+SUM(IF((B2=Dept_Id)*("Earnings"=Line_Descr)*("800215"=Account),1/COUNTIFS(Dept_Id,B2,Emplid,Emplid,Line_Descr,"Earnings",Account,"800215")),0)}


Any assistance would be greatly appreciated.
 
Hi, Sara!

Of how many cells are we talking about for the involved ranges? Depending on the amount of cells maybe 5' isn't anything abnormal. Had the chance to test it in other computer, preferably one more powerful if available?

Regards!
 
Hi SirJB7,


There are 15 ranges in all, each is usually around 3500 rows.

There are no other computers available to test on.


Looking at the formula I can see that they are repetitive, but I can't figure out how to simplify or combine the "or" portions.


Regards

Sara
 
Hi, Sara!


If -as I'm assuming- your named ranges are dynamically defined:

=OFFSET($B$2,,,COUNTA($B:B),COUNTA($1:$1))

would you try making a copy of the workbook and change their references to fixed ones like: =B2:B3452

just for a test?


Regards!


EDIT: I just found this:

http://www.decisionmodels.com/calcsecretsi.htm

The paragraph that says "One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions." Check if applies to any of your ranges, if so, adjust them to fixed and equal lengths when testing.
 
Hi SirJB7,


My ranges are not dynamic.


All ranges are fixed and equal lengths.


Account: =Details!$F$4:$F$3115

Acct_Desc: =Details!$G$4:$G$3115

Amount: =Details!$L$4:$L$3115

Dept_Id: =Details!$D$4:$D$3115

Emplid: =Details!$A$4:$A$3115

Ignore_Me: =Details!$O$4:$O$3115

Jobno: =Details!$B$4:$B$3115

Jrnl_Type: =Details!$N$4:$N$3115

Line_Descr: =Details!$K$4:$K$3115

Name_Report: =Details!$C$4:$C$3115

Paycode: =Details!$I$4:$I$3115

Paycode_Desc: =Details!$J$4:$J$3115

Paytype_Desc: =Details!$H$4:$H$3115

Period_End_Dt: =Details!$E$4:$E$3115

Units: =Details!$M$4:$M$3115


Looks like I might just have to put up with it :)

Cheers
 
I think you should be able to replace:

[pre]
Code: 
=ROUND(
SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800000")
+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800500")
+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800000")
+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800500")
+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800000")
+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800500")
+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800100")
+SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,"800215")
+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800100")
+SUMIFS(Units,Dept_Id,B2,Paycode,"N*",Account,"800215")
+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800100")
+SUMIFS(Units,Dept_Id,B2,Paycode,"R*",Account,"800215"),2)[/pre]

With:

=ROUND(Sumproduct((Dept_Id=B2)*(Line_Descr={"Earnings","N*","R*"})*(Account={80000,800500,800100,800215})*Units)
 
Hi Hui!


Thanks for that, unfortunately I'm getting #N/A as a result.


=ROUND(SUMPRODUCT((Dept_Id=B2)*(Line_Descr={"Earnings","N*","R*"})*(Account={800000,800500,800100,800215})*Units),2)


Would I be better off using helper columns in a data table?


Regards

Sara
 
Sara


Are you able to upload a sample file ?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

It will make it so much easier to assist you

Please randomise/anonymise the data as appropriate
 
Hello Sara,


Try this (untested)


=SUM(SUMIFS(Units,Dept_Id,B2,Paycode,{"N*","R*"},Account,{"800000";"800500";"800100";"800215"}),SUMIFS(Units,Dept_Id,B2,Line_Descr,"Earnings",Account,{"800000";"800500";"800100";"800215"}))


Note: In first SUMIFS Paycode criteria separator is comma & Account separator is semi-colon.


Hope this helps;

Haseeb
 
Hi there


Here is the link to the file

https://skydrive.live.com/redir?resid=9D4A15C8F236789E!112&authkey=!AA4elyD7lzSI9c4
 
Hi, Sara!


I didn't test Haseeb A's formula but downloaded your workbook, opened it in Excel, in a computer that was running heavy processes, (60/70% CPU and more than 85% disk), 2 browsers open (one with 12 pages and the other with 19) using 2Gb of RAM from my 6, Excel with two workbooks and VBA, same for Powerpoint, ... that's to say worse conditions than those of normal operation.


I pressed Ctrl-Alt-Shift-F9 to perform a full recalculation of the workbook (wider than usuals), and it took 1'09" once and 1'05" the second time. So considering the formulas you have I'd say that it isn't performing bad.


Would you describe on what hardware are you using this workbook and what percentages of resources' use are you experiencing? Thank you.


Regards!
 
Hi Sara / All ,


I cannot recommend this site enough ; it's a veritable treasure house.


If you use the Optimizer in it that is available for download :


http://ramblings.mcpher.com/Home/excelquirks/downlable-items/optimizeExample.xlsm?attredirects=0&d=1


you will see that monstrous though the formula you posted may look , it does not take up too much time ; the only time is in the formulae in columns K and L ; if you can find a way to modify those , you will see a marked improvement.


Narayan
 
Hi Sara ,


Can you check your file here ?


http://sdrv.ms/108uu0q


I have used a helper column on the Details tab.


Verify that the results on the Calcs tab are correct , and see if the time taken is the same as before or has it reduced.


Narayan
 
Hello Sara,


Could you try these formulas & see how calculation speed?


Assuming EmpID always be numeric. even those are formatted as text or having leading zeros.


B2;


Code: 
=ROUND(SUM(SUMIFS(Units,Dept_Id,A2,Paycode,{"N*","R*"},Account,{"800000";"800500";"800100";"800215"}),SUMIFS(Units,Dept_Id,A2,Line_Descr,"Earnings",Account,{"800000";"800500";"800100";"800215"})),2)


C2;


[code]=SUM(SUMIFS(Amount,Dept_Id,A2,Paycode,{"N*","R*"},Account,{"800000";"800500";"800100";"800215"}),SUMIFS(Amount,Dept_Id,A2,Line_Descr,"Earnings",Account,{"800000";"800500";"800100";"800215"}))


D2;


[code]=ROUND(SUM(SUMIFS(Units,Dept_Id,A2,Line_Descr,"Other Earnings",Paycode,{"L*","P*"},Account,{"800000";"800500";"800100";"800215"})),2)


E2;


[code]=SUM(SUMIFS(Amount,Dept_Id,A2,Line_Descr,"Other Earnings",Paycode,{"L*","P*"},Account,{"800000";"800500";"800100";"800215"}))


F2;


[code]=ROUND(SUM(SUMIFS(Units,Dept_Id,A2,Account,{"803500";"803700"})),2)


G2;


=ROUND(SUM(SUMIFS(Amount,Dept_Id,A2,Account,{"803500";"803700"})),2)


H2;


=ROUND(SUM(SUMIFS(Units,Dept_Id,A2,Paycode,"X*",Account,{"800000";"800500";"800100";"800215"})),2)[/code]


I2;


=SUM(SUMIFS(Amount,Dept_Id,A2,Paycode,"X*",Account,{"800000";"800500";"800100";"800215"}))[/code]


K2; with CTRL+SHIFT+ENTER as an Array


=SUM(IF(FREQUENCY(IF((Dept_Id=A2)*(Line_Descr="Earnings")*ISNUMBER(MATCH(Account,{"800000";"800500";"800100";"800215"},0)),Emplid+0),Emplid+0),1))[/code]


L2; with CTRL+SHIFT+ENTER


=SUM(IF(FREQUENCY(IF((Dept_Id=A2)*ISNUMBER(MATCH(Account,{"800000";"800500";"800100";"800215"},0)),Emplid+0),Emplid+0),1))[/code]


Hope this helps;

Haseeb
 
You lot are geniuses!

SirJB7 - the IT department are speeding up my pc...or replacing it

Narayan - Cheers - there's so much more to understand with array formulas

Haseeb - Changing to your formulas, it now calc's in seconds


Thanks to you, I can change a fair few of my other workbooks too.
 
Hi, Sara!

Glad you solved it. I never doubt about Haseeb A's formulas, he has that invisible touch... So credit to thim and your IT guys.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top