Counting Values
wow!
On 12 Gru, 18:27, Glenn wrote:
Jake wrote:
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 ***
|