# Trying to make a difficult count.

#### JesterX1313

##### New Member
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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:
Code:
``=OFFSET(test!\$A\$1,0,0,COUNT(test!\$A:\$A),1)``
3. Name the second range Words, defined as:
Code:
``=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):
Code:
``=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):
Code:
``=COUNTIF(H3:P3,">"&0)``
5. Copy that formula down.

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
ABCDEF
1
21applea3
31airb2
41balloonc2
52apple
62core
73apple
83balloon
93bags
103chips
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.

Very nice, Aladin. Much cleaner than my method.

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

Replies
7
Views
270
Replies
33
Views
173
Replies
3
Views
324
Replies
10
Views
155
Replies
15
Views
303

1,212,057
Messages
6,105,652
Members
447,974
Latest member
misspancake

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back