Counting Values

J

Jake

I am Working with range A1:A15. I would like to count how many times the
value (w) appears in this range. The formula would be in A16


Thank for your help
 
G

Glenn

Jake said:
I am Working with range A1:A15. I would like to count how many times the
value (w) appears in this range. The formula would be in A16


Thank for your help



Put this data in A1:A15:

down, DOWN, now, NOW, w, W, win, WIN, wow, WOW, www, WWW, AAA, BBB, CCC

Put w in B1. Depending upon what you want, use one of the formulas below (make
sure to commit the array-formulas with CTRL+SHIFT+ENTER).


1. Exact case as entire cell value (result = 1)

=SUM(--EXACT(A1:A15,B1))
*** array-formula ***



2. Either case as entire cell value (result = 2)

=COUNTIF(A1:A15,B1)



3. Starting with exact case (result = 4)

=SUM(--EXACT(LEFT(A1:A15,1),B1))
*** array-formula ***



4. Contains exact case (result = 6)

=COUNT(--(FIND(B1,A1:A15)>0))
*** array-formula ***



5. Starting with either case (result = 8)

=SUM(--(LEFT(A1:A15,1)="w"))
*** array-formula ***



6. Total occurrences of exact case (result = 9)

=SUM(LEN(A1:A15))-SUM(LEN(SUBSTITUTE(A1:A15,B1,"")))
*** array-formula ***



7. Contains either case (result = 12)

=COUNTIF(A1:A15,"*"&B1&"*")



8. Total occurrences of either case (result = 18)

=SUM(LEN(A1:A15))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:A15,LOWER(B1),""),UPPER(B1),"")))
*** array-formula ***
 
J

Jarek Kujawa

wow!

Put this data in A1:A15:

down, DOWN, now, NOW, w, W, win, WIN, wow, WOW, www, WWW, AAA, BBB, CCC

Put w in B1.  Depending upon what you want, use one of the formulas below (make
sure to commit the array-formulas with CTRL+SHIFT+ENTER).

1. Exact case as entire cell value (result = 1)

=SUM(--EXACT(A1:A15,B1))
*** array-formula ***

2. Either case as entire cell value (result = 2)

=COUNTIF(A1:A15,B1)

3. Starting with exact case (result = 4)

=SUM(--EXACT(LEFT(A1:A15,1),B1))
*** array-formula ***

4.  Contains exact case (result = 6)

=COUNT(--(FIND(B1,A1:A15)>0))
*** array-formula ***

5. Starting with either case (result = 8)

=SUM(--(LEFT(A1:A15,1)="w"))
*** array-formula ***

6. Total occurrences of exact case (result = 9)

=SUM(LEN(A1:A15))-SUM(LEN(SUBSTITUTE(A1:A15,B1,"")))
*** array-formula ***

7.  Contains either case (result = 12)

=COUNTIF(A1:A15,"*"&B1&"*")

8. Total occurrences of either case (result = 18)

=SUM(LEN(A1:A15))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:A15,LOWER(B1),""),UPPER(­B1),"")))
*** array-formula ***
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top