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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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

ADVERTISEMENT

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,136,518
Messages
5,676,324
Members
419,619
Latest member
jalme

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