need help

N

Narkom

Hello all, how can I do this:

I have names in column A (cell 3 till 100), and I want to count them.
But, i want that my output shows like this:
f.ex.:

madonna 12 (entrys)
abba 8
50 cent 3
etc.

Hope that someone can help me.
Sorry for my bad english.
 
K

Kevin Vaughn

I think you probably meant countif:

=COUNTIF(C3:C300,"Madonna") and I think the OP has the data in column A.
But that said, I probably would have used =countif(a3:a100, "abba") because I
love some of Abba's songs :)
 
K

Kevin Vaughn

Actually, come to think of it, you probably want the artist's name in a cell
and have the formula reference the cell, like this (assumes artist name in
B2):

=COUNTIF($a$3:$a$100,B2)
 
N

Narkom

the problem is that i must show the output in sheet 2 and all the dat
is in
sheet 1.
What if I dont´t know the names listed in the column A?
Thank you all for the hel
 
P

Pete_UK

You can extract a unique list of names from Sheet1 column A using Data
| Filter | Advanced Filter and electing to Copy to another location
(assume this is to Sheet2 column A). Then the formula becomes:

=COUNTIF(Sheet1!$A$3:$A$100,A2)

and this can be copied down for as many unique names as you have in
column A.

Hope this helps.

Pete
 
K

Kevin Vaughn

The first part is easy enough, just modify formula like so:

=COUNTIF(Sheet1!$A$3:$A$100, A2)

The 2nd part, not knowing the names, you "could" do in a couple of steps, if
you were so inclined. If you do data/filter/advanced filter, you can copy
unique records only to a range on the same worksheet, and then cut or copy
that over to sheet2.
If you were looking for a formula on sheet2 that could automatically do
that, I can't think of one off the top of my head. It should be relatively
easy to do using VBA, but I don't know how familiar/comfortable you are with
VBA. Someone else may come up with a solution using formulas.
 
K

Kevin Vaughn

I thought you could only copy to the same sheet. And when I just now tried,
I got an error message that seems to indicate that is true.
 
P

Peo Sjoblom

If you start from the other sheet by selecting a cell, then do
data>filter>advanced filter, do all the criteria then copy to another
location and once again select the destination sheet/cell it will work, you
can even copy to another workbook
--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
P

Pete_UK

Kevin,

whenever I want to extract unique values, I always copy the column(s)
into another sheet and then do Data | Filter | Advanced filter there,
usually copying into another location and then deleting the column(s)
with the original data in. This could form the start of Narkom's
Sheet2, or if he had already set something else up on that sheet then
the extracting could be done to Sheet3 and the unique list copied over
to Sheet2.

Hope this helps.

Pete
 
K

Kevin Vaughn

It took a little work to get it working as it seems Excel wanted me to have
selected a list first, but after a couple of false starts, I did get it to
work. Thanks.
 
N

Narkom

thank you all, now it works :)
but now when i want to calculate the entrys i must
do Data | Filter | Advanced Filter every time!
but what appens when i add new items to sheet1?
Is it possible that excel keeps sheet2 actual with all entrys of
sheet1?
I think on the calculate key f9.
Perhaps it is possible when i hit F9 that sheet2 makes an actualization
of sheet1?!
Sorry for my bad english
Hope you all understand me
Rui
 
P

Pete_UK

Starting with Sheet1 (only), you could record a macro while you:

insert a new Sheet2;
copy column A from Sheet1 to Sheet2;
apply Data | Filter | Advanced Filter copying your unique records to
Sheet2!$C$1;
delete columns A and B;
enter the formula in B2 and copy down.

You can allocate a shortcut key when you set up the macro (eg
CTRL-SHIFT-C), and in future, you could just re-run the macro with this
shortcut key whenever you add new items to Sheet1 - just rename or
delete Sheet2 first.

Hope this helps.

Pete
 
Top