Array Formula that Counts and Sums

ET Hug

Board Regular
Joined
Jun 3, 2005
Messages
111
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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Jun 3, 2005
Messages
111
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jun 3, 2005
Messages
111
Thanks for the tip Richard. :)
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Forum statistics

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

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