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

Why Every BI Professional Needs to Learn Agentic AI in 2026

Meta Description:  Agentic AI is transforming business intelligence. Learn why BI professionals must embrace autonomous AI agents to stay relevant — with practical examples, skills to build, and a BI Lead's honest perspective on the shift. Tags:   Agentic AI  ·  Business Intelligence  ·  Power BI  ·  AI Agents  ·  Data Analytics  ·  Future of BI  ·  Career Growth Let me be blunt: if you're a BI professional in 2025 and you haven't started paying attention to agentic AI, you're already behind. I'm not saying that to scare you. I'm saying it because I've spent over a decade building dashboards, tuning SQL queries, and wrangling Power BI data models — and nothing in my career has shifted the landscape as fast as agentic AI. Not self-service analytics. Not cloud migration. Not even the first wave of AI/ML. This is different. And here's why. What Exactly Is Agentic AI? Forget the chatbot hype for a second. Agentic AI refer...
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

Rethinking Agentic AI

 # Rethinking AI Agents: Why Intelligence Beats Integration Every Time ## More Tools Won't Save a Thoughtless Agent Every week, another development team ships an AI agent loaded with integrations — web search, vector databases, code runners, calendar hooks, payment gateways. The demo looks impressive. The stakeholders nod. Then the agent hits its first real user in a messy, unpredictable situation, and the cracks appear fast. The uncomfortable truth? Most AI agents fail not because they lack access to information, but because nobody taught them how to *think* about it. The industry has quietly developed a bad habit: treating agent-building like a hardware upgrade. Slow processor? Add RAM. Agent underperforming? Add tools. This logic sounds reasonable until you realize that intelligence doesn't accumulate through connection counts. A library card doesn't make someone well-read. --- ## Competence Isn't a Plugin Here's a useful mental test. Imagine hiring someone for a...
Post a Comment
Read more