Stuck on excel - Which formula?

[The Boosh!]

Hello

say I have a large list of data in one spread sheet. It contains a unique id number, a business, and job roles with in this business, with the employees name.

IE:
1234 Asda Cashier Bob Smith
1234 Asda Cashier Jim Bob
1234 Asda Cashier Alan Sugar
1234 Asda Manager Fred Rick
1234 Asda Manager Jak Bob
9789 Tesco Shelf Man Jim
9789 Tesco Shelf Man Jaz
9789 Tesco Shelf Man Luke

What I want to do is count how many Cashiers/Managers there is in total for that business, and bang it in another sheet.

For the example above, in the other sheet id have a column called "Cashier", and a column called "Manager", "Shelf man" etc etc

Down the left side I would have the Unique ID. I want to insert how many cashiers there are for this particular site.

Take asda for exmample, total cashiers at asda given the example above is 3.

_______Cashiers___ Managers___ Shelf men
1234 ___3___________2___________0
9789 ___0 ___________0___________3

(ignore the lines, it messed up my spacing so I had to put something there to make it stop)

Is there any way to make excel count words as such?

I can't get my head aronud how I am going to do it

Hope this makes sense?

Luke.

 

[Rob]

After a quick google, it threw up this, I have no idea if it works...

=SUM(COUNTIF(A1:A100,"Cat"),COUNTIF(A1:A100,"*Cat"))

or for more than one column aparently?

=sumproduct(--(countif(offset(a1:ch1,row(a1:a7)-row(a1),),"cat")>0))

FFS Do I have to do it all....

Exactly what you want

 

[Sarah]

=COUNTIF

 

[Sarah]

yeah it work

=countif(a:a,Manager)

Robs 1st formula is the easiest

Also you could do a pivot table with it

 

[The Boosh!]

Yes but it won't put it next to the unique ID will it?

I'm pretty sure I need to use a vlookup of some sort.

The sheet i'm on is fooking massive (about 4k+ rows) so can't really be pissing about doing manual count if's for each unique Id

 

[Gray]

you n00b....


Thats easy

 

[Sarah]

no not if your counting vlook up wont work

 

[Rob]

We're on form today Sarah.

 

[The Boosh!]

Burn excel with fire

 

[Sarah]

on the new sheet

=countif('othersheet'a:a,A),countif('othersheet'!B:B,'manager')

use your FX key if its exel

Edit @Rob- coz were the best

 

[Gray]

http://spreadsheets.about.com/od/excel101/a/Excel_beg_guide.htm

 

[The Boosh!]

on the new sheet

=countif('othersheet'a:a,A),countif('othersheet'!B:B,'manager')

use your FX key if its exel

Edit @Rob- coz were the best

LOL not really as it doesn't work.

If I just do a count if it will count how many Cashiers for example are in that column. It won't count JUST how many cashiers there are for that partucular site.

 

[Sarah]

ok i fail at that formula.....but its summit like that

know how to use a pivot table?

 

[Gray]

you need to associate the value of each site/building/department etc. with there unique id..

Give it me in an SQL DB and I would do it in seconds LOL, my excel skills aren't "all that" anymore x

 

[Gray]

ok i fail at that formula.....but its summit like that

know how to use a pivot table?

I told him this... but the answer is no, he doesn't haha

 

[Sym0n]

LOL didn't your placement specify that you had to have a good understanding of Excel?

http://tinyurl.com/2bc5tkt

You could go down the VLOOKUP path if you wanted or just autofilter and SUBTOTAL, but a pivot will get you what you need easily.

HTH

 

[Gray]

enjoying your time off work Simon?

 

[The Boosh!]

Worked the pivot table out. They're really good!

 

[Gray]

Worked the pivot table out. They're really good!

Told you..

Its the future

 

[Sarah]

Pivots FTFW - when u figure out how to use them they are the best

 

[Sym0n]

enjoying your time off work Simon?

Mate from the amount of calls and texts I'm getting about problems at work you'd think I'm not actually on leave. :nono:

Think I'm gonna take a trip to Renault to get an oil filter and turn my phone off whilst change it over. Make them panic for a bit lol.

 

[Gray]

LOL.... your just an important man x

 

[Sym0n]

This is true.