Efficiency question: SumIfs vs Array

[GDRitter]

I have a report I make for every month end. We dump tons of raw data out of SQL and then I slice and dice it into a table that the customer wants.

We're talking 200k rows of info across 25 columns.

I have to conditionally sum certain columns based on many logical checks on other columns. For this I've been using some big ugly array formulas. When I do this, it takes me probably 90 minutes to just fill the formulas down my table and then convert to values because the processing is so intense for my computer.

But it occurs to me today, that I should be able to produce the same results by using =SumIfs instead of an array. Would you expect this to be better performing, the same, or worse?

Here's an example of one of my array formulas.

=--SUM(([mainestats.xlsx]Sheet1!$T$2:$T$250000=$A$1)*([mainestats.xlsx]Sheet1!$B$2:$B$250000=$A13)*([mainestats.xlsx]Sheet1!$R$2:$R$250000="RECALL")*([mainestats.xlsx]Sheet1!$E$2:$E$250000))

I'll have a similar formula across 11 columns and copied down 24 rows (2 years of stats, by month). Then this will be duplicated across 10 or so tabs, one for each facility of this client that we service. If I try to fill my formulas on more than 1 tab at a time, it's basically hopeless. So I do one tab, wait a couple minutes, convert to values, do the second tab, wait a few minutes, convert to values, etc.

Just to clarify. My array formulas work completely right - they just are very slow to calculate and I'm wondering how to improve performance without asking the boss for a beefier computer. (I have a Core i5 with 8GB RAM laptop)

 

[63falcondude]

I would expect the use of sumifs formulas in place of array formulas to speed up your workbook significantly.

 

[FDibbins]

But it occurs to me today, that I should be able to produce the same results by using =SumIfs instead of an array. Would you expect this to be better performing, the same, or worse?

I would also expect better results using SUMIFS, but you know what - why not just try it and see?