Rob tient des statistiques sur chaque travail qu’il effectue chaque jour. Par exemple, il suit le nombre exact d’heures et de minutes passées sur chaque travail en entrant une heure de début (colonne B) et une heure de fin (colonne C) pour chaque travail au format: 16/05/11 11h25. Dans une colonne séparée (colonne G), il a la date pour chaque jour du mois. À droite de chaque date (dans la colonne H), il souhaite afficher le nombre de travaux qu’il a terminés à chacune des dates calendar. Rob se demande quelle formule il utiliserait pour calculer ce décompte.

Cette tâche n’est pas aussi facile que cela puisse paraître au premier abord. La raison en est la façon dont les heures de début et de fin sont stockées.

La colonne B contient à la fois la date et l’heure de début dans la même colonne, au format «16/05/11 11 h 25» et la colonne C contient à la fois la date et l’heure de fin dans le même format. Vous pourriez penser que vous pouvez utiliser la fonction COUNTIF dans la colonne G, de cette manière:

=COUNTIF(C$2:C$1000,G2)

Cela ne fonctionnera pas, cependant. La raison est simple: la colonne G (cellule G2, dans ce cas) contient une date, sans heure. Par exemple, il peut contenir la date 16/05/11. Lorsque cela est comparé à la cellule C2, qui pourrait contenir 16/05/11 11:25, ils ne sont pas les mêmes. N’oubliez pas qu’Excel effectue la comparaison en fonction du numéro de série de la date et de l’heure stockés dans chaque cellule. Étant donné les exemples de valeurs que nous venons de mentionner, la cellule G2 contiendrait un numéro de série de 39217 et la cellule C2 contiendrait un numéro de série de 39217.47569. Étant donné que ces deux valeurs ne sont pas identiques, COUNTIF ne les considère pas comme égales.

Évidemment, une solution consisterait à ajouter une autre colonne ne contenant que les dates de fin de chaque travail, sans heure. Ensuite, vous pouvez utiliser la fonction COUNTIF dans votre formule, car vous compareriez « des pommes à des pommes », pour ainsi dire. Mais il existe une solution qui ne nécessite pas l’utilisation d’un résultat intermédiaire dans une nouvelle colonne. Il s’agit de s’assurer que la valeur entière de tout ce qui se trouve dans la colonne C est comparée aux dates stockées dans la colonne G. Cela peut être fait en utilisant la fonction SUMPRODUCT, de cette manière:

=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)

L’entier de chacune des valeurs de la plage C2: C1000 est comparé à la date dans G2, donnant un tableau de valeurs Vrai et Faux. Multiplier chacun de ces éléments par 1 transforme les valeurs Vrai et Faux en valeurs 1 et 0, respectivement. La formule additionne ensuite ces produits, donnant le décompte souhaité.

Si vous préférez, vous pouvez également utiliser la formule suivante, qui effectue essentiellement la même tâche en utilisant SUMPRODUCT:

=SUMPRODUCT(--(INT(C$2:C$1000)=G2))

L’un des avantages de l’utilisation de la fonction SUMPRODUCT pour déterminer un résultat est que vous n’avez pas besoin d’utiliser une formule matricielle. Certaines personnes, cependant, préfèrent utiliser des formules matricielles. Si vous aimez les utiliser, vous pouvez utiliser l’une des méthodes suivantes:

=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))

=SUM(IF(ROUND(C$2:C$1000,0)=G2,1,0))

N’oubliez pas que les formules matricielles doivent être entrées dans une cellule en utilisant Ctrl + Maj + Entrée.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (10421) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.

Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Counting_Jobs_Completed_On_a_Date [Comptage des travaux terminés à une date].