Excel formula

[jen9814]

I need to get counts for various character fields within the same column. Below is some sample data.

Column a
task 1 - bob
task 2 - susan
task 3 - Jen
task 4 - Jon
task 5 - Bob
task 6 - bob
task 7 - jon
task 8 - jen

I would like to get a count for each person. In this example it would be Bob = 3, Susan = 1, Jen = 2, Jon = 2. Any help would be appreciated. Thanks.

 

[Adnane]

an alternative would be to first create a new column that would contain only the names (without Task1,Task2...). you could use this formula =RIGHT(A1,LEN(A1)-FIND("-",A1)-1)

Then you can use a pivot table to get all kind of statistics easily.

Hope this helps


Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[bluedragon2]

quick and easy wat is to do a text to columns with the - as a delimiter. Then do a =COUNTIF(B1:B8," name") for each name.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Hi,

Do you have task 1 - bob in a single cell? If so, NOT a good design.

If your data were in 2 columns
[tt]
A B
task 1 bob
[/tt]
it would be a snap using the COUNTIF function...
[tt]
=COUNTIF($B$1:$B$8,B3)
[/tt]


BTW, use the Tools/Text to Columns Wizard to parse your data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jen9814]

It is not possible to add a column because the columns represent a month. See below. Also the "-" isn't always present. Usually the names are in "()"s.

May 2004
task 1 - bob
task 2 - susan
task 3 - Jen
task 4 - Jon
task 5 - Bob
task 6 - bob
task 7 - jon
task 8 - jen

 

[SkipVought]

the columns represent a month

is a really poor spreadsheet design technique.

A much better design, in terms of sound database fundamentals, would be...
[tt]
TASK NAME DATE
task 1 bob 5/1/2004
task 2 susan 5/1/2004
task 3 Jen 5/1/2004
task 4 Jon 5/1/2004
task 5 Bob 5/1/2004
task 6 bob 5/1/2004
task 7 jon 5/1/2004
task 8 jen 5/1/2004
[/tt]
where 5/1/2004 is the data entry value and May 2004 is the Date Format Display value (in other words, almost ALWAYS use REAL Excel Dates, also)

This table format will make many other data analysis/reporting functions MUCH EASIER by virtue of the many reporting features available in Excel like...

Sort
Filter
Subtotal
Pivot Table Wizard
Chart Wizard

as well as many other database functions and table lookup functions.




Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jen9814]

I do not have ownership of the data; therefore changing the format is not feasible.

 

[KenWright]

You don't need to own the data in order to be able to change it. As to whether it is worth changing it or not depends on whether this is a one off or whether it needs to be done regularly. As long as you have access to the spreadsheet then you are perfectly capable of creating a copy and doing some data cleansing of your own, then utilising any of the suggestions suggested so far. If you are looking to add functionality to this spreadsheet for the owner, then the best thing you can do IMO is to go back to that person and show them the suggestions that have been made already wrt design and layout of the data.

If you really can't change anything, or even suggest changes, then you will have to live with what you have and see if you can't work with it as it is. That having been said, your statement about

>> Usually the names are in "()"s

worries me, as it would indicate that even in the format your data currently has, the records within it aren't even necessarily in a standard format. If this is the case then you are on a hiding to nothing before you even start. No matter what you do, if you are to collate data in any kind of meaningful way, then you need to impose certain standards and adhere to them. Analysis of this kind requires a discipline, and without it you will be plagued with problems.

To answer your very first note though, with the limited example given, assuming your data was in say A1:A100, and your names Bob, Susan, Jen, Jon etc were in C1:C10, then in cell D1 put the following formula

=COUNTIF($A$1:$A$100,"*"&C1)

Then copy that cell and paste it across D210

This will give you a count for the number of records for each person. Note though, that this assumes data is exactly as stated with the name occuring at the end. If the name could be anywhere in the data then you need to expand it somewhat as in

=COUNTIF($A$1:$A$100,"*"&C1&"*")

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

If your data is not consistant, then there will be no way to get a consistanty correct answer.

There is a therom of sorts in logic...

If your have an incorrect PREMISE (source data), no matter how correct you logic (macro) you will inevitably reach the wrong conclusion (results)

So AT A MINIMUM, the owner of the data/workbook, needs to GUARANTEE consistant data (like there's ALWAYS a dash preceeding the name). If the owner will not do THAT at a minimum, no effort will be sucessful!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[macropod]

Suppose you want your results to appear in B1:C4 as:

bob 3
susan 1
jen 2
jon 2

Then, in C1 enter the array formula:
=SUM(IF(RIGHT(TRIM(SUBSTITUTE(A$1:A$8,CHAR(160)," ")),LEN(B1))=B1,1,))
and copy down to C4.

Cheers
PS: The TRIM and SUBSTITUTE processes are only needed because the spaces in the original post continue beyond the names and are created as Char 160 instead of Char 32.