Excel: come contare quante volte compare un valore
Scopri come contare le occorrenze di un valore su Excel utilizzando la formula CONTA.SE o la funzione CONTA.PIÙ.SE e come contare i valori duplicati con CONTA.SE.
Microsoft Excel e’ uno dei programmi piu’ utilizzati per gestire e analizzare dati, perche’ consente di organizzare grandi quantita’ di informazioni in modo semplice ed efficace. Con lo strumento giusto, puoi facilmente contare il numero di volte che un determinato valore appare all’interno della tua cartella di lavoro.
Per fare cio’, ti verra’ in aiuto una delle due formule: CONTA.SE o CONTA.PIÙ.SE. La prima ti consentira’ di effettuare il conteggio delle occorrenze in un intervallo specifico, mentre la seconda sara’ utile se desideri effettuare il conteggio sulla base di diversi criteri contemporaneamente.
Iniziamo con l’utilizzo della formula CONTA.SE:
- Select the range where you want to count occurrences (for example a column)
- Type “=” on the adjacent cell and enter “COUNTIF”, followed by open and close parentheses after indicating the same range within quotes (“A:A”) followed by the value you want to search for also between quotes (“SearchValue”). The formula should look like this: =COUNTIF(“A:A”,”SearchValue”)
- – Click Enter button on your keyboard and you will have obtained the result of counting occurrences of the searched value in entire column A.
If instead you want to use COUNTIFS function to count based on multiple criteria, you will need to use a slightly different syntax. For example, if you want to count the number of times “SearchValue1” appears in column A and simultaneously “SearchValue2” in column B, follow these steps:
- Select the cell where you want to display the result of counting
- Type “=” on adjacent cell and enter “COUNTIFS”, followed by open and close parentheses.
- Indicate range for first column (“A:A”) within quotes then type comma (,) which will separate this range from another one based on which you want to perform count.
- Type within quotes header name for second Column (“B:B”) that you will be using as second search criterion. Insert a comma (,)
- Add between double quote both values being searched: each value should be entered individually preceded by “&” symbol. Therefore we will have: “&SearchValue1&”;”&SearchValue2&”
- Close parentheses with “)”
The formula should look like this: =COUNTIFS(A:A&B:B,”SearchValue1″&” SearchValue2″)
Finally, if you need to count duplicate values within same column or same previously selected interval for COUNTIF formula , here’s how proceed:
- Select the cell where you want to display the result of counting
Insert “=” into adjacent cell and enter “COUNTIF”, followed by open and closed parentheses.
Indicate range (“A:A”) between quotation marks, insert a comma (,), then indicate cell where comparison with other values in same column wants occur. In this case it is possible use “=” to identify all values that exactly match value in that particular cell.
Close parentheses with “)”. The formula should look like this: =COUNTIF(A:A;”=”&B1)
As you can see, counting occurrences of a value on Excel doesn’t require any special computer skills but only knowledge of the right formulas. Choose the one that best suits your needs and you will see how it can facilitate data management within your workbook.
#Excel #CONTASE #CONTAPIÙSE #valoriDuplicati #conteggioOccorrenze