XL 2013 sommeprod avec conditions

[davy76]

Bonjour a tous,

Je reviens vers vous avec un fichier dans lequel je dois retrouvé la somme des mes références par voie dans l'onglet base de comptage.
Jusqu'à présent il prend en compte le critère Commune et voie avec un si imbriqué qui prend en compte le fait que la colonne voie peut être vide.
Maintenant, c'est la que je bloque, il faut maintenant qu'il aille prendre en compte le critère borne pair et impair (Colonne E et F) en plus dans cette somme. Faire un sommeprod avec les bornes ca ne me pose pas de problème par contre, c'est le fait qu'il ne doit chercher que les pairs ou les impairs qui est plus délicat. Et mettre une condition si les Colonne E et F sont vides. Car si je prend l'exemple des lignes 80, 82 et 83 je retrouve le même résultats car il ne tient pas compte du bornage. Et sur le total, je me retrouve avec 737 référence dans l'onglet "Base de comptage" au lieu de 700 dans mon onglet "Travail.
J'espère avoir été clair.

Merci pour votre aide

 

[Nairolf]

Salut,

C'est vrai que c'est assez complexe, d'autant plus que la formule EST.IMPAIR() ne fonctionne pas en calcul matriciel, j'ai donc fait un calcul pour déterminer si c'est impair ou non.
Voici la formule qui en ressort :

=SI(C4="";SOMMEPROD((Travail!$A$2:$A$13900=A4)*1;Travail!$E$2:$E$13900);SOMMEPROD((Travail!$A$2:$A$13900=A4)*(Travail!$D$2:$D$13900=C4)*SI(D4="";1;SI(ESTNUM(Travail!$B$2:$B$13900);((2*ARRONDI.INF((Travail!$B$2:$B$13900+EST.IMPAIR(E4)*1)/2;0)-EST.IMPAIR(E4)*1)=Travail!$B$2:$B$13900)*(Travail!$B$2:$B$13900>=E4)*(Travail!$B$2:$B$13900<=F4);1));Travail!$E$2:$E$13900))

 

[davy76]

Oui, effectivement c'est assez complexe. Ta formule est intéressante mais ne fonctionne pas car, il ne compte plus les références dans le bornage.

Mais je pense que l'on se rapproche.
Merci

 

[Nairolf]

Je ne comprends pas car sur mon fichier, cela fonctionne j'ai les valeurs suivantes :
0,137842363
1,242724921
1,466549885

As-tu mis la formule en G4 et déroulé vers le bas?

 

[job75]

Bonjour davy76, Nairolf,

Voyez le fichier joint et les formules en C4 et G4 (pas besoin de la colonne H).

A+

 

[davy76]

Je ne comprends pas car sur mon fichier, cela fonctionne j'ai les valeurs suivantes :
0,137842363
1,242724921
1,466549885

As-tu mis la formule en G4 et déroulé vers le bas?

oui j'ai bien collé en G4, et j'ai toujours 0.

 

[davy76]

je joins mon fichier

 

[davy76]

Bonjour davy76, Nairolf,

Voyez le fichier joint et les formules en C4 et G4 (pas besoin de la colonne H).

A+

Merci Job75 mais mon résultat doit être égal a 700. et sur ton fichier le résultat est égal a 318.

 

[Nairolf]

Il faut que tu retires tous les @ qu'Excel à rajouté lorsque tu as copié la formule.

 

[davy76]

Il faut que tu retires tous les @ qu'Excel à rajouté lorsque tu as copié la formule.

Je ne vois pas de @

 

[job75]

Mon post #5 ne correspond pas au problème posé, n'en tenez pas compte.

Voyez ce fichier (2), les formules en colonne H sont matricielles : on ne peut pas utiliser SOMMEPROD car il y a des textes vides "" en colonne B de la feuille "Travail".

Le total de la 1ère feuille est un peu inférieur à 700, je ne peux pas vous dire pourquoi.

 

[davy76]

Ok je vais l essayer avec d autres données pour vérifier que ça fonctionne a chaque coup. Mais ça m a l air bien tout ça. Merci job75.

 

[job75]

Quand D4<>"" on a toujours C4<>"" j'ai donc simplifié la formule en H4 du fichier (2).

 

[job75]

Bonjour davy76, le forum,

J'ai étudié de près la colonne H de la 1ère feuille de mon fichier (2) post #11.

1) Sont comptés en trop (donc à déduire) :

0,000047228776668 en H219 ; 0,007025491696525 en H318.

2) Manquent (donc à ajouter) les adresses sans numéros de la colonne B 2ème feuille :

0,12973398854016 sur H96-H98 ; 0,00420795969356 sur H217-H218.

Au total on trouve bien 700 dans les 2 feuilles, plus exactement 699,99999999997.

A+

 

[davy76]

Merci a toi.