Skip to main content

Count Functions Uses in Microsoft Excel

 

1. Count No. of Unique Values

Use following formula to count no. of unique values -

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))


2. Count No. of Unique Values Conditionally

If you have data like below and you want to find the unique count for Region = “A”, then you

can use below Array formula –

=SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A

2:A20)-ROW(A2)+1),1))

If you have more number of conditions, the same can be built after A2:A20 = “A”.

Note - Array Formula is not entered by pressing ENTER after entering your formula but by

pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after

pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you

can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again.

Don't put { } manually.


3. Count Cells Starting (or Ending) with a particular String

1. Say you want to count all cells starting with C

=COUNTIF(A1:A10,"c*")

c* is case insensitive. Hence, it will count cells starting with both c or C.

Suppose you want to find all cells starting with Excel.

=COUNTIF(A1:A10,"excel*")

2. For ending

=COUNTIF(A1:A10,"*c")

c* is case insensitive. Hence, it will count cells starting with both c or C.

Suppose you want to find all cells starting with Excel.

=COUNTIF(A1:A10,"*excel")


4. Count No. of Cells Having Numbers Only

COUNT function counts only those cells which are having numbers.

Assuming your range is A1:A10, use following formula

=COUNT(A1:A10)


5. Count No. of Cells which are containing only Characters

Hence, if your cell is having a number 2.23, it will not be counted as it is a number.

Use below formula considering your range is A1:A10

=COUNTIF(A1:A10,"*")


6. Count Non Numbers in a String

Suppose you have a string "abc123def45cd" and you want to count non numbers in this.

If your string is in A1, use following formula in A1

=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(--NOT(ISNUMBER((--

MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))


7. Count Numbers in a String

Suppose you have a string "abc123def43cd" and you want to count numbers in this.

If your string is in A1, use following formula -

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

OR

=SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))


8. Count only Alphabets in a String

Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets.

Suppose your string is in A1, put following formula for this.

=SUMPRODUCT(LEN(A1)-

LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))

OR

=SUMPRODUCT(--(ABS(77.5-

CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))


9. COUNTIF on Filtered List

You can use SUBTOTAL to perform COUNT on a filtered list but COUNTIF can not be done

on a filtered list. Below formula can be used to perform COUNTIF on a filtered list

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))

Here B2:B20>14 is like a criterion in COUNTIF (=COUNTIF(B2:B20,">14"))


10 . COUNTIF for non-contiguous range

All of us love COUNTIF. And it is very easy to do - just say =COUNTIF("A1:A100",">5") and

it finds all the values within the range A1 to A100 which are greater than 5. But what if I

wanted the result for only A3, A8 and it should omit other cells. Try putting in following

formula -

=COUNTIF((A3, A8),">5") and it will give you #VALUE error.

A possible solution is

=(A3>5)+(A8>5)

What happens if you need to do for A3, A4, A5, A8, A24, A40, A45, A89. Now, you will have

to use a formula like -

=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)

The formula becomes cumbersome as the number of cells increase. In this case, you can use

below formula. This single formula can take care of contiguous (like A3:A5) and noncontiguous

ranges both -

=SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))


11. Count the Number of Words in a Cell / Range

Suppose you have been given the following and you need to count the number of words in

a cell or in a range.

Formula for calculating number of words in a cell -

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Formula for calculating number of words in a range -

=SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100),"

",""))+(TRIM(A1:A100)<>""))


Labels:

Comments

Post a Comment

Popular posts from this blog

Metabase - Open Source BI tool

 Metabase          Metabase is a business software suite and open source business intelligence framework. It allows users to store data about their business or a specific aspect of their business for their analytics in a secure data source. This intuitive software is designed to allow users to ask questions about their data and access answers that reduce jargon and code, making it very user-friendly and ideal for professionals who want to visualize their business’s data accurately                    Install in docker https://github.com/punitprabhu/MetabaseNew.git         Excecute in CLI docker run -d -p 3000:3000 --name metabase metabase/metabase         Host http://localhost:3000 ,           http://localhost:3000/browse/1-sample-database                         ...
Post a Comment
Read more

Quantum Computing

  Quantum computing is a new kind of computing that uses the laws of quantum physics to solve certain problems much faster than classical computers.  It doesn’t replace your laptop but can tackle very complex simulations, optimization, and cryptography‑style tasks that are intractable for ordinary machines.  *** ### What is quantum computing? Quantum computing is a computing paradigm that uses quantum‑mechanical phenomena—like superposition, entanglement, and interference—to represent and process information in new ways. Instead of classical bits (0 or 1), quantum computers use **qubits**, which can be in a mix of 0 and 1 at the same time, enabling parallel computation.  *** ### Classical bits vs. qubits - A **classical bit** is either 0 or 1; operations are deterministic and sequential.  - A **qubit** can be 0, 1, or any quantum “blend” of both, written as $$ \alpha|0\rangle + \beta|1\rangle $$, where $$ \alpha $$ and $$ \beta $$ are complex numbers capturing p...
Post a Comment
Read more

How to Choose a BI tool

 How to choose a BI Tool                                          In the process of choosing a Business Intelligence (BI) tool, here is a list of seven key capabilities that are recommended to consider. These capabilities will help you evaluate and select the best BI tool that aligns with your specific business criteria and goals:                            Data integration              Data management              Data warehouse              Data analysis              Data visualization              Automation              Data security              Licensing Cost...
Post a Comment
Read more