Large number of formulas cause recalc bottleneck...is there a better way?

[kweaver]

I have a large sheet (15,000+ rows by 82 columns). The cells each contain a formula that is a SUMIFS as noted below.
It's an awful recalc. I've been scratching my head to see if there's a better approach to this SUMIFS that'll be considerably faster.

Any thoughts?

=SUMIFS(SRBO!$F$2:$F$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$C$2:$C$15767,C$1)+SUMIFS(SRBO!$J$2:$J$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$G$2:$G$15767,C$1)+SUMIFS(SRBO!$N$2:$N$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$K$2:$K$15767,C$1)+SUMIFS(SRBO!$R$2:$R$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$O$2:$O$15767,C$1)+SUMIFS(SRBO!$V$2:$V$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$S$2:$S$15767,C$1)+SUMIFS(SRBO!$Z$2:$Z$15767,SRBO!$AR$2:$AR$15767,$A2,SRBO!$W$2:$W$15767,C$1)

More elaboration on what's being done:

SRBO, column AR has customer names. The sheet in column A has customer names. So, I need to find customer by customer.
Then, I need totals for each customer by adding the data in columns F, J, etc. if their product names in C, G, etc. [respectively] match the product headings in row 1. [make any sense?]

[Lugh]

Sounds like it would go much faster with Access DB used to store the info, feeding into Excel workbooks as needed.

[kweaver]

Lugh, thanks...that's the conclusion I came up w/today as well...I think I'll have to investigate that move. Nice to have some agreement.

[zeddy]

Hi Kevin

Why use scary SUMIFS when you could just do a quick convert to a pivot table?
With a little bit of vba you could automate the whole process.
In my test with 16,000+ records, it takes a blink of an eye.

See may attached file

First of all, save the attached file to a test folder of your choice.
Then rename the file extension from .pdf to .xlsb
Ignore the warning about changing file extension.
This is because the attached 2MB file is really an Excel file, NOT a pdf file.
In this Lounge, you can post larger files if they are "of pdf type" (you might have seen me use this trick to bypass the file-size limits here).

So, open the attached file in Excel, and you'll see it has a single sheet named [SRBO] with what I believe is a similar layout to your data.
Click the button [Product Sales by Customer] and the vba code will create new sheets [pivotData] and [pivot].

Voilla!

Is this something that you could use???

zeddy
Excel Combat Laundry Sheet Services

[zeddy]

Hi Kevin

So, if my file does the trick, just paste your [SRBO] sheet into the file and click the button.
etc etc etc.

zeddy

[kweaver]

GREAT IDEA, Zeddy.

With some minor adjustments at my end, this works just great!

I need another version of this as well, and think I can modify this accordingly for the 2nd approach.

YAY!

[kweaver]

What's the downside, if any, of saving a workbook as an XLSB? If not downside, why not do this by default?

[Paul T]

XLSB is binary format instead of text and can be more efficient for large files.
https://stackoverflow.com/questions/...ormats-be-used

cheers, Paul

[zeddy]

Hi Kevin

Paul has provided a great link discussing the .xlsb format.
This is my file format of choice, because the file-size is smaller and you can also have macros in the file.
Smaller file-sizes means network traffic is less overloaded, smaller files load faster etc etc etc.
A minor downside (in my opinion) is that if your file gets corrupted, it is tougher to forensically recover useful data from a binary file. But then you would have a better backup policy than relying on forensic data recovery!

zeddy

[zeddy]

Hi Kevin

So, you liked my method then!
And the .pdf attachment trick worked.

For the benefit of others, I used your SUMIFS formula in post#1 to work out
columns C, G, K, O, S, W had Product Type info
columns F, J, N, R, V, Z have Amounts

Re: I need another version of this as well
Perhaps your data has Qty columns as well as Amounts, and you need to check number of Product units shipped to Customers. I'm guessing here, but, assuming this is the case, the following attached file will also generate another sheet [SalesByQty] with analysis of Product Qty per Customer.

I previously assumed that there would be "no gaps" in the data records so I used the end(xlup) method in each column to append the data. This is probably not a good idea.
So I generated some new test data records that had data gaps within the Customer record rows.
The processing now takes this into account.

I also added a couple of coding tweaks to change numberformats, freezing the window headings etc etc etc.

So, as before, save the attached file to a folder of your choice, and rename the file from .pdf to .xlsb.
Is this version any better?
Is my dummy data good enough?

zeddy
Excel Prophetic Layout Estimators

[kweaver]

Your sample file(s) were PERFECT and allowed me to accomplish what I needed! GREAT PDF trick as well!

You're work is amazing and greatly appreciated.

I don't think the wine I'd send you would travel well across the pond.

EDIT: Here's part of an adjustment I need but cannot tell where I need to adjust the macro.
Suppose I now want another column on the "pivot" output (suppose it's to be column B and the other shift over 1 to allow for this.
In that column, which I want to be able to cherry pick from a drop-down, it's "source" which comes from SRBC, column B. I stumbled on a few tries of my own, but they didn't work.

[zeddy]

Hi Kevin

If I ever get to La Jolla I would very much look forward to drinking that wine!

Now, just to be clear, are you wanting to a have a pivot table 'page' field?
For example, if your [SRBO] sheet has a Source column B (let's say, US State data), you want to include this column in the pivot data source block, and then want to select the Source from a Page-field dropdown in the pivot table, and see the corresponding data in the pivot table?

zeddy
Excel Threat Identification Unit Leader

[kweaver]

Yes, you stated it correctly. (I renamed SRBO to SRBC, but that's no issue).
The B column in SRBC has the "source" in it (I might want to add 2 or 3, but i suspect if I know how to add 1, I can add 3).
The product names are currently in C, G, K, etc. and the totals are in F, J, N, etc.
AR still has the customer name in it.


P.S. Where will ALWAYS be a great wine available whenever you make it here (or even close...I'll drive to meet up with you).

/K

[zeddy]

Hi Kevin

OK, give me a moment to update the code, run a test or two, and post back an updated sample file for you.

zeddy

[zeddy]

Hi Kevin

..here's the v3 with a Pivot Page field for selecting Source.

Let me know if this does what you are looking for.
You can search the vba code for v3 to see the changes I made etc etc etc

zeddy