Trying to make a difficult count.

JesterX1313

New Member
Joined
Dec 12, 2003
Messages
10
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
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:
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top