Sumifs vs Sumproduct

[helpexcel]

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.

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

 

[jasonb75]

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]

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]

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.