Compteu el total de coincidències en dos intervals

= SUMPRODUCT ( COUNTIF (range1,range2))

Per comparar dos intervals i comptar les coincidències totals entre els dos intervals, podeu utilitzar una fórmula que combina el fitxer Funció COUNTIF amb el Funció SUMPRODUCT . A l'exemple que es mostra, la fórmula de la cel·la G7 és:

 
= SUMPRODUCT ( COUNTIF (B5:B11,D5:D11))

Nota: a aquesta fórmula no li importa la ubicació ni l’ordre dels elements de cada interval.

Suposem que teniu una llista 'mestra' d'algun tipus i que també teniu una altra llista que conté alguns dels mateixos elements. Voleu una fórmula que compare els valors de la segona llista per veure quants d'ells apareixen a la primera llista. No us importa l’ordre en què es troben els articles, només voleu saber quants elements de la llista 2 apareixen a la llista 1.

com afegir la validació de dades a Excel

La funció COUNTIF comptarà les coses d’un interval que compleixin els vostres criteris. Normalment, assignareu a COUNTIF un interval com A1: A10 i un criteri senzill com '> 10'. A continuació, COUNTIF retornaria el recompte de cel·les de A1: A10 superiors a 10.

En aquest cas, però, donem a COUNTIF un abast per criteris. No fem servir cap operador lògic, cosa que significa que COUNTIF comprovarà l’equivalència (és a dir, es comporta com si féssim servir l’operador igual (=)).

Com que donem a COUNTIF un interval (també anomenat 'matriu') que conté 7 elements, COUNTIF retornarà una matriu de 7 elements com a resultat. Cada element de la matriu de resultats representa un recompte. A l'exemple, aquesta matriu que retorna COUNTIF té aquest aspecte:

com fer línies de tendència en Excel

{1101100}

com crear percentatges a Excel

Ara només hem de sumar els elements d’aquesta matriu, que és un treball perfecte per a SUMPRODUCT. La funció SUMPRODUCT és una funció versàtil que gestiona matrius de forma nativa sense cap sintaxi de matriu especial.

Si doneu a SUMPRODUCT dues o més matrius, multiplicarà les matrius juntes, resumirà els resultats i retornarà aquest número. En aquest cas, donem a SUMPRODUCT només una matriu, de manera que simplement resumeix els elements de la matriu i en retorna 4 com a resultat.

Coincidència entre files

Si voleu comparar dos intervals o columnes i voleu comptar coincidències al nivell de la fila (és a dir, només comptar coincidències quan aparegui el mateix element a la mateixa posició), necessitareu un fórmula diferent .