• 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.

avoiding excel to recalculate too much

A

aurisabelle

New Member
Hello,


I really need your help. I have been using sumifs formula for large spreadsheets, it recalculates taking to long come up with answers. Is there a better way to handle data that will be adding more rows. Here is my formula. Sorry for being too big but is the only way Im finding to be working for the report that I need.


Thank you very much for your help.


=IF(AND($E$7="Borderless Networks",$E$8="'----------------------------"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Borderless Networks",$E$8="Enterprise Networking (Other)"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$B:$B,"Enterprise Networking (Other)",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Borderless Networks",$E$8="Enterprise Networking (Routing)"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$B:$B,"Enterprise Networking (Routing)",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Borderless Networks",$E$8="Enterprise Networking (Switching)"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$B:$B,"Enterprise Networking (Switching)",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Borderless Networks",$E$8="Security"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$B:$B,"Security",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Borderless Networks",$E$8="Unified Access (Wireless)"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Borderless Networks",'LAT YTD Bookings'!$B:$B,"Unified Access (Wireless)",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Collaboration",$E$8="Contact Center"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Collaboration",'LAT YTD Bookings'!$B:$B,"Contact Center",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Collaboration",$E$8="Core Collab"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Collaboration",'LAT YTD Bookings'!$B:$B,"Core Collab",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Collaboration",$E$8="Video"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Collaboration",'LAT YTD Bookings'!$B:$B,"Video",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Data Center",$E$8="Data Center Other"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Data Center",'LAT YTD Bookings'!$B:$B,"Data Center Other",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Data Center",$E$8="DCS"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Data Center",'LAT YTD Bookings'!$B:$B,"Data Center Other",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Data Center",$E$8="Storage"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"Data Center",'LAT YTD Bookings'!$B:$B,"Data Center Other",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),IF(AND($E$7="Data Center",$E$8="UCS Plus"),SUMIFS('LAT YTD Bookings'!$AG:$AG,'LAT YTD Bookings'!$A:$A,"UCS Plus",'LAT YTD Bookings'!$B:$B,"Data Center Other",'LAT YTD Bookings'!$C:$C,"Product",'LAT YTD Bookings'!$H:$H,BB$11,'LAT YTD Bookings'!$N:$N,$B49),0)))))))))))))
 
Hi, aurisabelle!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


That's what I definitively should call a little tiny IF...


I don't know how many rows do you have in our 'LAT YTD Bookings' worksheet but instead of referring to the whole column ($AG:$AG, $C:$C, ...) I'd begin defining dynamic named ranges so as to:

- restrict calculations only to the used rows

- maintain the functionality of adding rows and not having to change formulas


If you could manage to handle the changes, give this a try first. If not, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Good morning SirJB7


"call a little tiny IF..." more of a speech impediment I think, aurisabelle


must have a stutter,ifffffffff. :)
 
Hi ,


Going by the way your formula has been phrased , the slowdown is because there is a tremendous amount of repetition.


The easiest way to resolve this is to use helper columns.


The first thing to do is to resolve the repetition of AND($E$7=....,$E$8=....) by setting up a table as follows :

[pre]
Code: 
Borderless Networks   ----------------------------
Borderless Networks   Enterprise Networking (Other)
Borderless Networks   Enterprise Networking (Routing)
Borderless Networks   Enterprise Networking (Switching)
Borderless Networks   Security
Borderless Networks   Unified Access (Wireless)
Collaboration             Contact Center
Collaboration             Core Collab
Collaboration             Video
Data Center               Data Center Other
Data Center               DCS
Data Center               Storage
Data Center               UCS Plus
[/pre]
This table can be easily accessed using a MATCH statement to retrieve the various combinations.


The second thing that can be done is to use helper columns for the various combinations within the SUMIFS statements , since there is a lot of repetition there too.


If you can upload a sample workbook with just 10 rows of data , it should be possible to redo the formulae.


Narayan
 
Hello Narayan,


Thank you so much for your quick reply. I am not seeing where to upload the file. I am not sure if I can use match for a combination of 3 sets of list for each combination of products reason why it repeats in the formula.


I will wait for your indications of how to upload the file.


Isy
 
Hi Isy ,


This forum does not have any provision for attaching files to your posts , or uploading files to the forum.


What you can do is detailed here : http://chandoo.org/forums/topic/posting-a-sample-workbook


Just upload your file to your favourite file-sharing website , give others permission to download your file , and then post that access link here , in this same topic. Ensure that this file-sharing website does not require others who wish to download your file , to register first. For instance , 4shared.com requires the user to register , whereas 2shared.com does not. You are at liberty to use your own preferred website.


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