Compteu les cel·les que continguin x o y

= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',rng)) +  ISNUMBER ( FIND ('def',rng)))>0))

Per comptar les cel·les que contenen x o y, podeu utilitzar una fórmula basada en Funció SUMPRODUCT . A l'exemple que es mostra, la fórmula de la cel·la F5 és:

 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11))+ ISNUMBER ( FIND ('def',B5:B11)))>0))

Aquesta és la solució de fórmula d’una sola cèl·lula, que s’explica a continuació. També és possible utilitzar una fórmula més senzilla basada en columna auxiliar , també s’explica a continuació.

Quan es compten les cel·les amb 'lògica OR', cal anar amb compte de no comptar-ne el doble. Per exemple, si compteu cel·les que contenen 'abc' o 'def', no podeu afegir dues funcions COUNTIF perquè podeu comptar dues vegades les cel·les que contenen tant 'abc' com 'def'.

Solució d'una sola cèl·lula

Podeu utilitzar una fórmula única SUMPRODUCT amb NÚMERO + TROBAR . La fórmula de F5 és:

 
= SUMPRODUCT (--(( ISNUMBER ( FIND ('abc',B5:B11)) +  ISNUMBER ( FIND ('def',B5:B11)))>0))

Aquesta fórmula es basa en la fórmula explicat aquí que localitza el text dins d'una cel·la:

 
 ISNUMBER ( FIND ('abc',B5:B11)

Quan se li dóna un interval de cel·les, aquest fragment retornarà un matriu de valors TRUE / FALSE, un valor per a cada cel·la de l'interval. Com que ho fem servir dues vegades (una per a 'abc' i una per a 'def'), ho aconseguirem dos matrius.

A continuació, afegim aquestes matrius juntes (amb +), cosa que crea una nova matriu única de nombres. Cada número d'aquesta matriu és el resultat d'afegir junts els valors TRUE i FALSE a les dues matrius originals. A l'exemple que es mostra, la matriu resultant té aquest aspecte:

 
{2020102}

Hem de sumar aquests números, però no volem que es comptabilitzi el doble. Per tant, hem d’assegurar-nos que qualsevol valor superior a zero només es compti una vegada. Per fer-ho, forcem tots els valors a TRUE o FALSE amb '> 0' i, a continuació, forcem a 1/0 amb el doble negatiu (-).

Finalment, SUMPRODUCT retorna la suma de tots els valors de la matriu.

Solució de columna auxiliar

Amb una columna auxiliar per comprovar cada cel·la individualment, el problema és menys complex. Podem utilitzar COUNTIF amb dos valors (proporcionats com a 'constant de matriu'). La fórmula a C5 és:

 
=--( SUM ( COUNTIF (B5,{'*abc*','*def*'}))>0)

què és el gràfic circular en Excel

COUNTIF retornarà una matriu que conté dos elements: un recompte per a 'abc' i un recompte per a 'def'. Per evitar el doble recompte, afegim els elements i, a continuació, forcem el resultat a TRUE / FALSE amb '> 0'. Finalment, convertim els valors TRUE / FALSE a 1 i 0 amb un doble negatiu (-).

El resultat final és 1 o 0 per a cada cel·la. Per obtenir un total de tot cel·les de l’interval, simplement sumeu la columna auxiliar.