# Sumif / Sumproduct / Array

#### thorpyuk

##### Well-known Member
Which formula is the most efficient? I need to put quite a lot of them into a spreadsheet :s

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### DonkeyOte

##### MrExcel MVP
SUMIF if you don't have more than 1 criteria.

How many and what are the calulcations you intend to do ?

You may find there are efficient work arounds you can do creating Unique references (concatenated) which will allow you to use a SUMIF rather than having to use SUMPRODUCT.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Depends what you are doing... SUMPRODUCTS are handy when you have mulitple criteria summations / counts to perform. SUMIF / COUNTIF recalculate quicker than SUMPRODUCT in my experience. I only use array formula when I absolutely have to - they really do seem to slow my models down...

Perhaps someone can suggest which is the most effiocient formula to use if you describe what you are doing in a little more detail.

#### xld

##### Banned
As mentioned, if there is only one criteria, SUMIF/COUNTIF will beat SUMPRODUCT hands down for speed. These functions are optimised under the sheets to test a criteria over an array, SUMPRODUCT uses the array approach, testing them one at a time.

You can also simualte multiple conditions if they are simple using say COUNTIF. For instance, this SP formula

=SUMPRODUCT(--(A2:A200>=--"2008-01-01"),--(A2:A200<--"2008-02-01"))

can be achieved with

=COUNTIF(A2:A200,">=01/01/2008")-COUNTIF(A2:A200,">=01/02/2008")

and the latter is still quicker

#### Aladin Akyurek

##### MrExcel MVP
Which formula is the most efficient? I need to put quite a lot of them into a spreadsheet :s

When there is just one condition to evaluate, a formula with SumIf is almost always more appropriate to invoke.

An array/matrix formula with If for conditional calculations is more general for it can be combined Sum, Max, Min, StDev, Median, Average, etc.

A SumProduct formula for (straight) multi-conditional counting and summing often slightly more efficient than an equivalent Sum/If formula, if set up appropriately.

#### thorpyuk

##### Well-known Member
Thanks for all your responses

Replies
18
Views
140
Replies
1
Views
176
Replies
12
Views
97
Replies
3
Views
109
Replies
6
Views
529

Threads
1,191,687
Messages
5,988,024
Members
440,125
Latest member
vincentchu2369

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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