La funzione COUNTIFS di Excel viene utilizzata per ottenere il numero di celle in uno o più intervalli che soddisfano una serie di condizioni o criteri specificati.
Occasionalmente, la funzione COUNTIFS non funziona come previsto e restituisce un #VALORE ! 0 , un messaggio che indica la presenza di un errore nella formula o di un altro valore inaspettato.
Ecco alcuni dei motivi per cui la funzione non funziona come previsto in Microsoft Excel:
- Quando una formula fa riferimento a un intervallo in una cartella di lavoro chiusa.
- Utilizzo di un valore da un’altra cella senza concatenarlo all’operatore logico utilizzato.
- Non racchiudere i criteri di testo tra virgolette (” “).
- Gli intervalli successivi non hanno lo stesso numero di colonne e righe del primo intervallo di criteri.
- I criteri numerici e l’operatore logico non sono racchiusi tra virgolette (” “).
- Cercare di ottenere un numero utilizzando la logica OR.
- Modifiche successive al set di dati.
- Errori nella formula.
In questa esercitazione spieghiamo come risolvere questi problemi in modo che la funzione restituisca il numero corretto che stiamo cercando.
1. La formula fa riferimento a un intervallo in un’altra cartella di lavoro che è chiusa.
Quando la funzione COUNTIFS fa riferimento a un intervallo in un’altra cartella di lavoro chiusa, restituisce un errore #VALORE! invece del numero di celle previsto.
Nell’esempio seguente, invece della formula che restituisce il numero di prodotti rossi, è stato generato un errore #VALORE! perché la formula fa riferimento a un intervallo in un’altra cartella di lavoro chiusa.la sua cartella di lavoro chiamata Colori prodotti è chiusa.
Soluzione
La soluzione al problema dei conti non funzionanti in questo scenario consiste nell’aprire la cartella di lavoro chiusa e premere il tasto F9 sulla tastiera in modo che la formula ricalcoli i parametri corretti. Si otterrà così il conto corretto, come mostrato nell’esempio seguente.
2. Utilizzo del valore di un’altra cella senza concatenare il riferimento alla cella all’operatore logico
Se in una formula si utilizzano un operatore logico e un riferimento di cella come criteri per la funzione CONTABILI e non si concatena il riferimento di cella all’operatore logico inserendo & prima del riferimento di cella, la funzione restituirà uno 0 invece del numero previsto.
Ciò è illustrato nel seguente esempio in cui è stata utilizzata la formula =COUNTIFS(G2:G6,”>M2″).
Soluzione
Nell’esempio seguente, dobbiamo inserire la formula corretta =COUNTIFS(G2:G6,”> ” &M2) nella cella I3. Quando si preme INVIO , si ottiene il numero corretto di 4.
Nella formula corretta, solo l’operatore logico maggiore di (>) è racchiuso tra virgolette (” “) e l’operatore di concatenazione & è posto prima del riferimento alla cella M2.
3. Non mettere le virgolette (” “) intorno ai criteri di testo
Se i criteri di testo non sono racchiusi tra virgolette, la funzione COUNTIFS restituirà il valore 0 invece del numero corretto che ci aspettiamo, come mostrato nell’esempio seguente.
La formula nella cella J2 è =COUNTIFS(C2:C6,Rosso). La formula restituisce 0 perché il criterio Rosso non è stato racchiuso tra virgolette (” “).
Soluzione al problema dei conti che non funzionano
È necessario racchiudere i criteri di testo nella formula tra virgolette, come mostrato nell’esempio seguente, e si otterrà così il numero corretto di 2.
4. La serie successiva di criteri non ha la stessa forma della prima serie di criteri.
Quando si utilizzano più criteri nella funzione COUNTIFS e gli intervalli di criteri opzionali aggiuntivi non corrispondono all’intervallo del primo criterio, viene generato l’errore #VALORE.
Nell’esempio seguente, nella cella G3 è stata inserita la formula =COUNTIFS(B2:B13, “Rosso”,C2:C10, “Microsoft”) nel tentativo di ottenere il numero di prodotti rossi forniti da Microsoft.
Quando si preme Invio, si ottiene l’errore #VALORE! come mostrato di seguito.
Questo perché il secondo intervallo dei criteri C2:C10 non ha lo stesso numero di celle del primo intervallo dei criteri B2:B13.
Soluzione
Per correggere questo problema, è necessario assicurarsi che il secondo intervallo di criteri abbia le stesse dimensioni del primo, come mostrato di seguito:
Quando si preme INVIO, si ottiene il numero corretto di 2, come mostrato di seguito:
5. Non racchiudere i criteri numerici e l’operatore logico tra virgolette (” “)
Quando in una formula si utilizzano operatori logici come uguale a (=), maggiore di (>), minore di (<) e non uguale a (<>) e non si inseriscono i criteri numerici e l’operatore logico tra le stesse virgolette, Excel invia un messaggio che indica che la formula contiene un errore.
Nell’esempio seguente, non abbiamo inserito l’operatore logico e il criterio numerico tra virgolette. Solo l’operatore logico è racchiuso tra virgolette.
Quando si preme INVIO, si ottiene il seguente messaggio:
Soluzione
Dobbiamo assicurarci di includere sia l’operatore logico sia il criterio numerico tra le stesse virgolette, come mostrato di seguito:
Quando premiamo INVIO, otteniamo il numero corretto di 5, come mostrato di seguito:
6. Cercare di ottenere un numero utilizzando la logica OR
Sebbene la funzione COUNTIFS sia in grado di generare conteggi corretti utilizzando la logica AND, genera solo un valore 0 quando si cerca di utilizzarla per calcolare con la logica OR.
Nell’esempio seguente, stiamo cercando di ottenere il numero di categorie di prodotti rossi e blu utilizzando la logica OR.
Quando si preme INVIO, si ottiene il valore inaspettato di 0, come mostrato di seguito. Questo perché la funzione COUNTIFS non può calcolare con la logica OR.
Soluzione
È possibile aggirare il problema della logica OR utilizzando le funzioni COUNTIFS e SUM insieme.
Nell’esempio seguente, abbiamo inserito la formula =SOMMA(COUNTIFS(B2:B13,{“Rosso”, “Blu”})) nella cella G3.
Quando si preme INVIO, si ottiene il valore corretto di 6, come mostrato di seguito. Ciò è dovuto al fatto che la funzione COUNTIFS ha restituito 3 numeri di rossi e 3 numeri di blu dall’intervallo di criteri B2:B13 e la funzione SOMMA ha sommato i valori per ottenere 6.
7. Modifiche successive al set di dati
Le modifiche apportate a un set di dati possono far sì che la funzione COUNTIFS non generi i valori previsti. Ad esempio, nel seguente set di dati, la funzione COUNTIF funziona correttamente e restituisce il numero corretto di prodotti blu.
Ma se i dati vengono modificati, ad esempio in questo caso aggiungendo la parola colore a Blu, la funzione COUNTIFS visualizza il valore errato 0.
Soluzione
Questa situazione può essere risolta utilizzando i caratteri jolly con la funzione COUNTIF.
Nell’esempio seguente, abbiamo utilizzato il carattere asterisco (*) nei criteri, che ci permette di ottenere il numero corretto di 4 prodotti. La funzione COUNTIF può ora cercare corrispondenze parziali nell’intervallo di criteri.
8. Errori nella formula
A volte la funzione COUNTIF non funziona a causa di un errore nella formula, ad esempio la mancanza di una virgola di separazione tra l’intervallo di criteri e l’operatore logico.
Nell’esempio seguente, non c’è una virgola tra l’intervallo di criteri D2:D13 e l’operatore logico maggiore di (>).
Quando si preme INVIO, viene visualizzato un messaggio che informa della presenza di un errore nella formula.
Soluzione
È necessario prestare particolare attenzione quando si scrivono le formule per assicurarsi che la sintassi sia corretta. Nell’esempio seguente, l’inserimento di una virgola tra l’intervallo di criteri e l’operatore maggiore di (>) risolve il problema e si ottiene il numero corretto, 5.
Conclusione
COUNTIFS è una funzione molto potente che può essere utilizzata per generare il numero di celle in intervalli di dati che soddisfano condizioni specifiche.
In questa esercitazione abbiamo esaminato i vari motivi per cui questa funzione non funziona sempre come previsto e i modi per risolverli.