XL 2010 La fonction SOMME.SI.ENS() me renvoie l'erreur #VALEUR!

[IxeX]

Bonjour à tous !

Je me permets de vous soumettre un petit problème concernant la fonction somme.si.en(). La formule ci-dessous me retourne l'erreur #VALEUR!

=(SOMME.SI.ENS('D1:F1'!$B$1:B999999;'D1:F1'!$A$1:A999999;">"&Résultat!A1))

J'effectue mon traitement dans les onglets Feuil1, Feuil2, Feuil3. Ces onglets se situent entre les onglets D1 et F1.

Depuis mon fichier d'exemple (ci-joint), J'affiche mon résultat en B1 (depuis l'onglet "Résultat") où j'additionne toutes les valeurs en B (des feuilles contenues entre les onglets D1 et F1) si les dates en A (des feuilles contenues entre les onglets D1 et F1) sont supérieures à la date de recherche, à savoir en A (depuis l'onglet "Résultat").

PS : Si j'effectue mon traitement sur une seule feuille, la formule fonctionne.......

Avez-vous une solution/explication à me proposer et/ou une alternative commode afin de contourner ce problème ?

Merci d'avance !

 

[job75]

Bonjour IxeX,

La formule en B1 n'a pas de sens mais vous pouvez faire la somme de 3 formules telles que B2.

A+

 

[IxeX]

Bonjour IxeX,

La formule en B1 n'a pas de sens mais vous pouvez faire la somme de 3 formules telles que B2.

A+

Bonjour job75 et merci de votre retour.

L'intérêt de la formule en B1 (et de cette fonction SOMME.SI.ENS()) est de pallier à l'ajout et/ou retrait de feuilles entre les onglets D1 et F1... sans pour autant revenir à la formule même en B1.

Après, s'il est impossible d'organiser une formule dans ce sens, je passerai par votre solution d'appoint.

 

[vgendron]

Hello le fil

il me semble avoir lu quelque part que les formules "3D" sont très limitées en terme de fonctions utilisables...
https://support.microsoft.com/fr-fr/office/cr%C3%A9er-une-r%C3%A9f%C3%A9rence-3d-%C3%A0-la-m%C3%AAme-plage-de-cellules-dans-plusieurs-feuilles-de-calcul-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

 

[chris]

Bonjour à tous

Pour faire une formule 3 D avec SOMME.SI il faut une liste des onglets ou des tableaux structurés (il serait peut-être temps de les utiliser...) et combiner avec SOMMEPROD

 

[job75]

Re, bonjour vgendron, chris,

S'il y a beaucoup de feuilles à tester le plus simple est d'utiliser cette fonction VBA :

Function MaSomme#(coltest%, colresu%, test$)
Application.Volatile
Dim nf$, w As Worksheet, r As Range, e As Boolean
nf = Application.Caller.Parent.Name
On Error Resume Next 'si e n'est pas booléenne
For Each w In Worksheets
    If w.Name <> nf Then
        For Each r In w.UsedRange.Rows
            e = False
            e = Evaluate(r.Cells(coltest).Value2 & test)
            If e Then MaSomme = MaSomme + CDbl(r.Cells(colresu))
        Next r
    End If
Next w
End Function

Voyez le fichier joint, le code doit être placé impérativement dans un module standard.

Formule en A1 de la feuille "Résultat" =MaSomme(1;2;">"&A1)

A+

 

[job75]

Cela dit la fonction précédente n'est pas très rapide car chaque ligne est testée avec Evaluate.

 

[job75]

Cela dit la fonction précédente n'est pas très rapide car chaque ligne est testée avec Evaluate.

Voyez le fichier (2), avec ce nouveau code qui utilise SOMME.SI le calcul est très rapide :

Function MaSomme#(coltest%, colresu%, test$)
Application.Volatile
Dim nf$, w As Worksheet
nf = Application.Caller.Parent.Name
For Each w In Worksheets
    If w.Name <> nf Then MaSomme = MaSomme + _
        Evaluate("SUMIF(" & w.Columns(coltest).Address(External:=True) & ",""" & test & """," & w.Columns(colresu).Address(External:=True) & ")")
Next
End Function