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

Year Over Year calculation using Pivot Table

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area. Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this: Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As. Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK. You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.
Post a Comment
Read more

Power Point - Slide Master a very important tool

  When you want all your slides to contain the same fonts and images (such as logos), you can make those changes in one place—the Slide Master, and they'll be applied to all your slides. To open Slide Master view, on the   View   tab, select   Slide Master : The master slide is the top slide in the thumbnail pane on the left side of the window. The related layout masters appear just below the slide master (as in this picture from PowerPoint for macOS): 1  Slide master 2  Layout masters When you edit the slide master, all slides that are based on that master will contain those changes. However, the majority of changes that you make will most likely be to the layout masters related to the master. When you make changes to layout masters and the slide master in Slide Master view, other people working in your presentation (in Normal view) can’t accidentally delete or edit what you’ve done. Conversely, if you're working in Normal view and find that you'...
Post a Comment
Read more