SOMME.SI.ENS Plages variables et critères élaborés

[Delsaufa]

Bonjour à tous,

Et merci d'avance à qui voudra m'aider.

Je cherche à obtenir une formule SOMME.SI.ENS assez complexe (pour moi) et conséquente avec une plage critère et une plage somme dynamique, ainsi que des critères élaborés.
Dans mon tableau exemple à trouver en pièce jointe :
Dans ma feuille « Descriptif » je cherches à obtenir 3 formule en A5 (pour les EAUX USEES), en A7 (pour les EAUX PLUVIALES) et en F5 (pour les EAUX POTABLES) (chaque formule sera très similaire), et en fonction des désignations à trouver dans la feuille « DQE ».

Pour ma « plage_somme » et ma « plage_critères1 » :
Je souhaite ne pas prendre entièrement les colonnes de C à E (à partir de C23) de la feuille DQE, je souhaites que la plage soit dynamique et que celle-ci commence à un titre (EAUX PLUVIALES par exemple, chaque titre est remplie de couleur jaune) et que la plage se finisse au titre suivant (une ligne avant si possible), j’ai une formule pour trouver un titre :
=ET(NBCAR($C23)<50;NBCAR($C23)>1;NBCAR($B23)<2;NBCAR($D23)=0;NBCAR($C22)=0;NB.SI($C23;"<>*TOTAL*");NB.SI($C23;"<>*MONTANT*"))
(Nombre de valeur inférieur à 50 en colonne C ; Nombre de valeur inférieur à 2 en colonne B ; Nombre de valeur nulle en colonne D ; Nombre de valeur nulle en colonne C et ligne -1 ; Ne pas confondre un titre avec un total donc la cellule ne contient ni le mot « Total » ni le mot « montant »)
Il faudrait donc ajouter que la valeur recherchée dans le titre ici est «*EAUX PLUVIALE* » par exemple ou « *EAUX USEE* » ou « *EAUX POTABLE* », chaque valeur aura une formule spécifique.
Je pense avoir besoin des fonctions INDEX et EQUIV ici mais je ne suis pas encore à l’aise avec cette fonction.

Pour mon « critères1 » (si possible) :
Normalement mon critère doit être un mot ou un chiffre présent dans la colonne C, ici mon critère est un diamètre (Ø) par exemple « *200* », cependant est t’il possible de ne pas mettre un critère unique (« *200* ») mais une plage de nombre comme «*<300*» ? Pour prendre tous les diamètre de 0 à 300 mais de manière lisible pour Excel bien sûr...
Je cherche 3 formules, pour obtenir les quantités de tout type de matériaux au diamètre :

• Inférieur ou égal à 300 ;
• Supérieur à 300 mais inférieur ou égal à 500 ;
• Supérieur à 500.

Donc au final :
On aurait au total 9 (gigas) formules
3 formules pour :
3 « critères » différentes (=<300 ;>300 et =<500 ;>500)
Et sur
3 « plage_critères » et « plage_somme » différentes (Plage EAUX PLUVIALES ; EAUX USEES ; EAUX POTABLES)

++

 

[mapomme]

Bonjour @Delsaufa , bienvenue sur XLD ,

Je suis un peu revenu des formules "à rallonge". J'ai utilisé 3 colonnes auxiliaires sur la feuille "DQE". Ces colonnes vont me servir dans la formules de cellule A5 de la feuille "Descriptif". La cellule L1 de la feuille "Descriptif" permet de sélection de la condition à respecter. Il n'y a donc qu'une formule paramétrée par la cellule L1. Voir le fichier joint.

 

[Delsaufa]

Bonjour @mapomme et merci ,

Je viens d'ouvrir ton fichier solution, il est vraiment super, pas parfait je vais modifier la cellule L1 car je devrais avoir les réponses pour les 3 types de Ø différents présents en même temps dans la cellule et pas en fonction de L1, mais ça je vais me débrouiller à faire ça lundi. De même, je n'avais pas précisé mais je cherchais avec avoir les quantités dans le descriptif et pas les prix, donc ça j'ai juste à changer la dernière plage des formules en A5, A7 et F7.

Donc pour le moment ça me va, merci beaucoup pour tes formules intéressantes.

Bonne journée à toi et aux membres du forums.