# Sumifs vs Sumproduct

#### helpexcel

##### Well-known Member
Hi - what are the pro/con of each of these? I believe sumproduct will still pull data from a closed sheet, whereas sumif will not. I'm not sure either is faster or uses less resources than the other on large data sets. I'm also using VBA to populate these formulas with the code below. I've used both options, and they are both painfully slow.

VBA Code:
``````Option 1
With wsC
.Range(.Cells(3, 26), .Cells(LastrowC, LastColumn)).Formula = "=SUMPRODUCT((Data1=\$O3)+0,(Data2=\$D3)+0,(Data3=Z\$2)+0,(Data4))"
.Range("X3:X" & LastrowC).Formula = "=sum(Z3:AM3)"

End With

Option 2
With wsC
.Range(.Cells(3, 26), .Cells(LastrowC, LastColumn)).Formula =
“=SUMIFS(Data4,Data1,\$O3,Data2,\$D3,Data3,Z\$2)”
.Range("X3:X" & LastrowC).Formula = "=sum(Z3:AM3)"

End With``````

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### jasonb75

##### Well-known Member
Sumifs is more efficient than sumproduct.

Without an indication of the number of cells that you're applying the formula to or the time that the code takes to run, I wouldn't like to say specifically how efficient it is or isn't for your purpose.
Applying the formula to 100k cells, with Data1, etc all referring to full columns, perhaps go for lunch while the code runs.

Are you using `Application.Calculation = xlManual` and `Application.ScreenUpdating = False` to speed up your code execution?
If you do use them remember to use `Application.Calculation = xlAutomatic` and `Application.ScreenUpdating = True` at the end of your code to return to normal state.

#### helpexcel

##### Well-known Member
It's over 200k rows. I'm trying to update the sumif code to something i just learned, i'll see if that helps. I know it's going to take awhile, was hoping it would be mins, not hours.

#### jasonb75

##### Well-known Member
200k rows and 14 columns if I'm reading your code correctly. That means 2.8 million formulas, with that in mind hours would be expected.

Noting that you appear to have named ranges, are they dynamic? If not, making them so could help.

Replies
2
Views
357
Replies
7
Views
66
Replies
5
Views
94
Replies
3
Views
151
Replies
5
Views
255

### Forum statistics

1,127,306
Messages
5,623,875
Members
416,000
Latest member
Sovereign maphoso

### 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