Making sumifs les heavy to calculate or using other way

[aurisabelle]

Hello,

I am using sumifs to manange a large amount of data that have additional rows every time, so I am using the whole column to capture all data even if I am in row 200,000. It works great but it recalculates and takes seconds to update. Is there a better way to do what I need to do so it is smoother, less heavy to the file and that will not recalculate as much.

Thank you very much.

Below is the formulas that I am using, it is long but It's working but slowly?

=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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$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,BC$11,'LAT YTD Bookings'!$N:$N,$B49),0)))))))))))))

 

[Derek Brown]

Hello and welcome to The Board.
This is a quick answer without studying your formula too much, but I would avoid referencing the whole column particularly if using Excel 2007/2010 where you could be referencing 1.1 million rows.
Instead I would consider using a dynamic range (have a look at the dynamic charts section at Jon's Excel Charts and Tutorials - Index for details of the technique) together with SUMPRODUCT (have a look at examples of this powerful function at http://www.xldynamic.com/source/xld.SUMPRODUCT.html).
If you want to check performance of worksheets and don't mind paying for it, have a look at FastExcel at Advanced Microsoft Excel Consultancy and Solution Development -Decision Models.