XL 2013 Compter des projets (sans doublons) correspondant à certains critères

[Chrystel01]

Bonjour,

Je souhaiterais dans le cas ci-dessous compter le nombre de projets et de lots ,(quel que soit le propriétaire du projet-Col A) qui ont un impact dans la transition ( commentaire apparaissant dans la colonne D), et cela sans compter les doublons.

Je vous joint le fichier correspondant.
Je vous serai très reconnaissante si vous pouviez m'aider sur le sujet car je ne m'en sors pas.

Je vous remercie par avance

Chrystel

 

[JHA]

Bonjour à tous,

Pas certain de tout comprendre la demande

=SOMME(ESTTEXTE(D2:D8)*(1/NB.SI(B2:B8;B2:B8)))

=SOMME(ESTTEXTE(D2:D8)*(1/NB.SI.ENS(B2:B8;B2:B8;C2:C8;C2:C8)))

JHA

 

[Chrystel01]

Bonjour JHA,
Bonjour le Forum,

Vous avez bien compris mon souhait :
totaliser le nombre de projets et le nombre de Projets/lots qui ont un commentaire dans la colonne transition, sans compter les doublons.

Je vous remercie beaucoup pour votre formule magique !
J'essaye de la comprendre pour pouvoir la réutiliser en d'autres circonstances mais j'ai un peu de mal (désolée)...
- la 1ère partie ESTTEXTE(D29) regarde s'il y a un commentaire
-pourriez-vous svp m'expliquer la 2ème partie : 1/NB.SI(B2:B9;B2:B9) ?

Par ailleurs, j'ai essayer de la tester en ajoutant un commentaire (onglet test1), puis 1 ligne (onglet test2) => cela fonctionne parfaitement.
Par contre je ne sais pas pourquoi quand j'insère une ligne (ex test3 - ligne en rouge), le nb de projets affiche un chiffre à virgule (3.5 au lieu de 4)?

Merci d'avance pour votre aide
Bonne journée

Chrystel

 

[ALS35]

Bonjour à tous,
Pour le nombre de projets, une proposition :

=SOMMEPROD((SIERREUR(1/NB.SI.ENS(B2:B9;B2:B9;D2:D9;"<>");0)*(D2:D9<>"")))

Cordialement

 

[Chrystel01]

Bonjour ALS35,

Merci beaucoup pour ta réponse. Ta formule sur les projets fonctionne parfaitement.
Saurais tu stp me l'expliquer ? J'avoue que j'ai du mal à la comprendre...
et j'aimerais être capable d'utiliser ce type de formule seule, dans un autre contexte.

Merci beaucoup
Bonne soirée

Chrystel

 

[JHA]

Bonjour à tous,

1/NB.SI(B2:B8;B2:B8)

=NB.SI(B2:B8;B2:B8) te renvoie la matrice {1;2;2;1;1;1;1}
=1/NB.SI(B2:B8;B2:B8) te renvoie la matrice {1;0,5;0,5;1;1;1;1}
comme tu peux le voir le 1est divisé par tous les chiffres de la matrice et on en fait la somme (1/2=0.5)

mais le formule de @ALS35 basée sur le même principe est plus appropriée.

JHA

 

[ALS35]

Bonjour à tous,

En complément de la réponse de JHA,

Dans la formule :
=SOMMEPROD((SIERREUR(1/NB.SI.ENS(B2:B9;B2:B9;D29;"<>");0)*(D29<>"")))

NB.SI.ENS(B2:B9;B2:B9;D29;"<>") est une formule matricielle qui calcule pour chaque ligne du tableau le nombre de fois ou l'on a un projet donné avec un commentaire renseigné (soit n).
1/NB.SI.ENS(..) l'inverse soit 1/n, on aura donc n lignes valant 1/n, la somme finale vaudra donc 1.
Dans le cas ou le nombre n calculé est égal à 0 , cas d'un projet sans commentaire renseigné, 1/NB.SI.ENS(...) renvoie une valeur d'erreur division par zéro. À l'aide de la fonction SIERREUR(...;0) on remplace cette valeur d'erreur par 0 afin de ne pas impacter la somme finale.
Ensuite on ne considère que les lignes pour lesquelles un commentaire existe : *(D29<>"") et on fait la somme du tout avec SOMMEPROD ou SOMME.
L'utilisation de SOMMEPROD permet de s'affranchir de la validation matricielle par Ctrl+Maj+Entrée qui est nécessaire avec SOMME dans ta version 2013.

Ce n'est pas une formule si facile que ça à comprendre et à maitriser, il faut y aller par étape.

Cordialement

 

[Chrystel01]

Bonsoir ALS35,
Bonsoir JHA,
Bonsoir le forum,

Je vous remercie beaucoup pour vos formules et explications détaillées.
Elles me seront très utiles aujourd'hui et dans le futur si j'arrive à le refaire car elle n'est pas simple.

Je vous souhaite une excellente fin de semaine

Chrystel