# Array Formula that Counts and Sums

#### ET Hug

##### Board Regular
Hello Everyone,

I want to divide 30,000 by a number of similar items in another column.

Eg.
Column A
Name
Tony - is written 3 times, then 30,000 /3
Peter - is written 6 times, then 30,000 /6

Can anyone help me build a formula for this? It looks like a combination of Count and Arrays. I can't build an array if my life depended on it.

Thanks

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### NBVC

##### Well-known Member
Something like?

=30000/Countif(A1:A100,"Tony")

where A1:A100 is the range in Column A

You can replace "Tony" with a cell reference housing the string Tony.

#### ET Hug

##### Board Regular
Thanks NVBC. Theres quite a few names to go through. I was hoping it could've been done easier. Oh well, I have the afternoon to plug away.

#### Richard Schollar

##### MrExcel MVP
You can use a cell reference as the criteria eg if cell B1 held the name you wanted to count then:

=30000/Countif(A1:A100,B1)

This could speed things up for you if you have a list of the relevant names, as you can copy the formula down.

Richard

#### ET Hug

##### Board Regular
Thanks for the tip Richard.

#### NBVC

##### Well-known Member
Thanks NVBC. Theres quite a few names to go through. I was hoping it could've been done easier. Oh well, I have the afternoon to plug away.

I did mention that you can replace "Tony" with a cell reference housing the string.

...You can replace "Tony" with a cell reference housing the string Tony.

#### ET Hug

##### Board Regular
Thanks NVBC

Appreciate both of your help.

Replies
2
Views
371
Replies
7
Views
263
Replies
3
Views
83
Replies
14
Views
552
Replies
5
Views
697

Threads
1,170,944
Messages
5,872,879
Members
432,951
Latest member
darellparker80

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

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