Array Formula that Counts and Sums

[ET Hug]

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

 

[NBVC]

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]

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]

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]

Thanks for the tip Richard.

 

[NBVC]

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]

Thanks NVBC

Appreciate both of your help.