# Efficiency question: SumIfs vs Array

#### GDRitter

##### New Member
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.

Code:
``=--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)

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### 63falcondude

##### Well-known Member
I would expect the use of sumifs formulas in place of array formulas to speed up your workbook significantly.

#### FDibbins

##### Well-known Member
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?

Replies
22
Views
845
Replies
21
Views
675
Replies
2
Views
102
Replies
6
Views
350
Replies
7
Views
145

1,191,614
Messages
5,987,690
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back