Trying to make a difficult count.

[JesterX1313]

Ok, I have an interesting one. If anyone could help, that would be great. I'll try to describe this as best as possible. I have 2 rows. Looks like this...

A B
1 apple
1 air
1 balloon
2 apple
2 core
3 apple
3 balloon
3 bags
3 chips

What I'm looking for is something that will allow me to count the number of times a number has a letter (first letter of column b). So 1, 2, and 3 have the letter a so I'd want the count to show 3 for a, 2 for b, 2 for c. I can't figure out how to make it count without counting the mulitple times a shows up in one. Also (if it's possible, and if not, no big deal) count this even if the numbers are not in any order. Please let me know if this made any sense. Thanks for any help.a

 

[gardnertoo]

I made the following assumptions:
a. The number/word combinations you list in the question are only the beginning of your data set; now or in the future the list will grow down the sheet as you add more number and words
b. You will use larger numbers in the future, not just 1,2,3.
c. You will enter words with any first letter, not just a,b,c.
d. The data begins in row 1 with no header row.
e. The worksheet is named "test"

Here you go:

Create named ranges

1. Insert\Name\Define
2. Name the first range Numbers, defined as:

=OFFSET(test!$A$1,0,0,COUNT(test!$A:$A),1)

3. Name the second range Words, defined as:

=OFFSET(test!$A$1,0,1,COUNT(test!$A:$A),1)

Create a workspace to mine the data

1. Fill the range G3:G28 with the letters a-z
2. Fill the range H2:P2 with the numbers 1-9 (this can be extended as far to the right as you need)
3. In cell H3 enter the following ARRAY FORMULA (you must hit CTRL+SHIFT+ENTER, not just enter, for this to work):

=SUM((numbers=H$2)*(LEFT(words,1)=$G3))

4. Copy this formula across and down by dragging the corner of cell H3, just like you would for any normal formula.

Create the final list

1. Enter in cell C1 the heading "Letter"
2. Enter in cell D1 the heading "Number of #'s which have that letter"
3. Fill the range C2:C27 with the letters a-z
4. In cell D2 enter the following formula (not an array):

=COUNTIF(H3:P3,">"&0)

5. Copy that formula down.

 

[Aladin Akyurek]

Ok, I have an interesting one. If anyone could help, that would be great. I'll try to describe this as best as possible. I have 2 rows. Looks like this...

A B
1 apple
1 air
1 balloon
2 apple
2 core
3 apple
3 balloon
3 bags
3 chips

What I'm looking for is something that will allow me to count the number of times a number has a letter (first letter of column b). So 1, 2, and 3 have the letter a so I'd want the count to show 3 for a, 2 for b, 2 for c. I can't figure out how to make it count without counting the mulitple times a shows up in one. Also (if it's possible, and if not, no big deal) count this even if the numbers are not in any order. Please let me know if this made any sense. Thanks for any help.a

Book1
	A	B	C	D	E	F
1
2	1	apple			a	3
3	1	air			b	2
4	1	balloon			c	2
5	2	apple
6	2	core
7	3	apple
8	3	balloon
9	3	bags
10	3	chips
11
12
Sheet1

F2:

Cpontrol+shift+enter...

=SUM(IF(FREQUENCY(IF(LEFT($B$2:$B$10)=E2,IF($A$2:$A$10<>"",MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))

then copy down.

 

[gardnertoo]

Very nice, Aladin. Much cleaner than my method.

 

[JesterX1313]

Hey, thanks a lot guys. This is great stuff. You guys rock.