SumIF, 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?

Enclosed the Excel filePièce jointe 150062

hello,
You're on a francopphone forum.
Please make the effort to post in French so that members can understand you.

Bonjour

Pour cet outil budget, j ai 2 feuilles Excel: l une pour saisir des donnees (CPB90), la seconde pour le reporting (liquidity plan) , qui somme les donnees saisies par compte et mois avec SOMMEIF et INDIRECT (car je peux avoir plusieurs feuilles de saisie et le nom de chacune me sert dans la feuille liquidity plan a rechercher les montants). +SOMME.SI(INDIRECT("'" & $E$3 & "'!$A11:$BQ800");$D17;INDIRECT("'" & $E$3 & "'!F11:F800")).
Cette formule est utilisee pour un grand nombre de cellules (152 lignes , 14 colonnes), ce qui cree un pbl de performance (6 sec calculation pour chaque changement). Je voudrais eviter le set up vba (calcmanual), peut etre avez vous une solution pour reduire le temps de traitement afin d etre proche d 1 seconde pour que l utilisateur ne doive pas attendre autant)?

Bonjour,

Tu peux essayer la fonction personnalisée suivante :

Code:

---

1 2 3

Function Somme_si_feuille(Feuille As String, Plage_critere As Range, Critere As Range, Plage_somme As Range) Somme_si_feuille = WorksheetFunction.SumIf(Sheets(Feuille).Range(Plage_critere.Address), Critere, Sheets(Feuille).Range(Plage_somme.Address)) End Function

---

Ensuite, utilise la comme une somme.si, sauf qu'en premier argument tu auras la feuille sur laquelle se basent les plages somme et critere


Ex : =somme_si_feuille("Feuil2";Feuil2!A3:A9;A3;Feuil2!B3:B9)
Poulpe

Merci pour ta réponse mais ce que je cherche à faire, c'est quand je suis dans la feuille "liquidity plan" d'aller recherche dans la feuille CPB90 tous les enregistrements correspondant au critère $E$3 (de la feuille "liquidity plan") en sommant des colonnes de la feuille CPB90 (mois de l'année).

Par ailleurs le temps de réponse est toujours élevé (6-8 sec) lorsque je fais F9 (recalculate).

Ci-joint la feuille Excel (la précédente n'était pas la bonne).

Bonjour,
Plusieurs secondes pour sommer quelques centaines de cellules est certainement du à un problème de conception.
Le problème est dans la volatilité de la même fonctions utilisée dans toutes les cellules qui seront toutes recalculées même quand leurs dépendances ne sont pas affectées.
Essaies de contourner en essayant d'obtenir la valeur renvoyée par la fonction INDIRECT dans une unique cellule pointée par les autres de la feuille.
Il y a certainement des solutions plus légères.
J'aurais aimé aidé mieux. Si tu tentes de nous poster les formules et les indications utiles, pour m'épargner cette pénible peine de devoir télécharger ton fichier exemple.

Bonsoir,

Si j'ai bien compris, tu as plusieurs scénarios qui sont chacun contenus dans une feuille. Tu veux que l'utilisateur puisse sélectionner un scénario, ce qui change les données sources.

La solution qui me semble la plus évidente, ce serait de faire que tes cellules se réfèrent à une plage spécifique (exemple feuil1 A1:A3) que tu nommerais plage_source par exemple, et qu'une macro puisse modifier la FEUILLE à laquelle se réfère la plage_source, par exemple pour qu'elle devienne feuil2 A1:A3. Une solution qui marche uniquement si tu te réfères systématiquement aux mêmes plages dans toutes tes formules, sinon il faudrait définir des centaines de noms et ça deviendrait ingérable.

Peux-tu me confirmer que j'ai bien compris, et me dire si la solution que je propose semble te convenir?

Poulpe

Merci a vous deux, probleme resolu. J ai egalement joue sur le calc manuel et activesheet.calc / enable calculation false pour optimiser le traitement.