VBA Formula SUMIF with INDIRECT performance issue

Hello

For a budget tool, I have 2 sheets: one to input data (CPB90) and the second one to report data (liquidity plan) that sums input data per account / month with SUM.IF and using Indirect : +SOMME.SI(INDIRECT("'" & $E$3 & "'!$A11:$BQ800");$D17;INDIRECT("'" & $E$3 & "'!F11:F800")).

This formula is needed for a high number of cells in Liquidity Plan sheet (152 rows x 14 columns) creating performance issues : 6 sec at every change in CPB90.

I read a few discussions but cannot find a way to reduce significantly this (to be instantaneous "1 sec" so that users doesn't have to wait).

Any idea?

Thanks for reply .

EN formula is +SUM.IF(INDIRECT("'" & $E$3 & "'!$A11:$BQ800");$D17;INDIRECT("'" & $E$3 & "'!F11:F800")).

Tes I can set calc as manual during input time, but even though when I refresh, than it's still 6 secondes. It doesn't solve the problem. I am asking if there is a way to reduce the processing time (whether calc automatic or calc manula+F9).

If you open the attached file, go to CPB90, and enter a figure in CPB90, cell G12 for instance (then F9), you'll see the processing time.

Fred

Here is the Excel file.
Cannot copy paste since I need to sumif several lines from CPB90 sheet (let's say 15 lines for same account) => 1 line in Liquidity Plan file.
You can see in the Excel file.Classeur1.xlsm

Also, the criteria range should be restricted to only column A, not A:BQ
Frankly I'm surprised it works at all with it like that.
The SumRange and Criteria Range need to be the same dimensions.

Also need to lock the Row#s in the ranges.
And you don't need the sheet references in the formula, since that is being added via the UDF.

Try
=Somme_si_feuille($E$3,$A$11:$A$200,$D17,F$11:F$200)

Thanks,guys. It works out just fine. I used your input, but i also set the manual calculation and use the activesheet.calculate only when necessary. All combined , now it takes 1 sec or less to display the figures. Thread can be closed.

You're welcome.