I'm struggling with a creating a formula that I can use to count how many people have attended a workshop each month from a directorate.

Essentially I need to work out from the screen picture attached how many people 'Booked' (column D) 'Leading in Clarks' (Column B) from 'Finance / Human Resources' (Column G).

Any idea of the formula I can use that would use that criteria to give me how many people booked that course?

_________________
The stats below lie. I'm actually much much worse.

You used words in your database, are these the labels of codes (for example 1 = booked and 2 = cancel)?
If you have not used codes, I would do so first. Its very hard to make computers run formulas if they are not in numbers.
Most programs wont allow you too do so anyways.

If you code booked / cancel/ what other you have in numbers 0,1,2 and do the same for finances 0=no 1=yes for example.
Then select cases that meet both criteria (select cases > when criteria is satisfied > booked =0 & finances= 1, btw thats an example of course depending on the number you actually choose)
You can then split the file on the criteria 'month'.
Then you can do whatever you like, count, find avarage per month, etc etc.

I find this very hard to explain in english.
However for running stuff like this... these kind of things are very easy to do in spss, i find that much easier to use then exel.If you only need this answer, I could probably do it for you in 1 minute if you sent me the database.

(I hope you understand my point, I mean you should not make a formula, but should make a selection criteria and then split or aggregate based on month depending on if you want absolute or relative numbers)

Really dont use exel for this... if you dont have spss i believe microsoft office has another program for databases, I dont know what it is called. Otherwise get it spss from somewhere and import the database in there. Then you'll find what you look for very easily. I only use exel for my graphs not for any statistics really.

I hope this made any sense. x

http://support.microsoft.com/kb/214153

?

"excel + count string occurences"

_________________
slurper

m3n you always have the best avatars

_________________
-"You've really worked out your banter, haven't you?"
-"No, not really. This is a different thing, it's spontaneous and it's called wit."

Yeah basically, but he needs to count those under certain criteria, in this case it must forfill 2 others before counted.
(number if a1-a200=0 & if d1-D100=1 for example)
Thats very nasty to do, also he would still need to recode it I think.

recoding isn't an option as its a data dump from a course booking system. I tried Countif and got completed lost but perhaps I can try a bit harder.

_________________
The stats below lie. I'm actually much much worse.

Here's a quick if slight ungainly fix:

Concatenate the 3 columns you want to query and then do a COUNTIF against that. So in the next empty column (say it was column M)you would do:

=CONCATENATE(B1, D1 , G1) {and fill it down the screen}

Then in the next column do:

=COUNTIF(M:M,"BookedLeadin in ClarksFinance / Human Resources")

That will give you a count of what you want

_________________
"Fire rained from the sky on the day I was born: 10,000 lives I ended before drawing first breath. Do not speak to me of guilt or regret, Jonathan.” - Brayan, The Keepers of the Fire

You cant make new target variables or labels in exel? Really? Think that would be a fail for a statistsics program.
What Howard suggests should work as well, fact is other programs do this automatically for you, so you only have to ask it what you want instead of making rules / formula's. If you dont get it to work give me a shout.

Thanks Howard and everyone else = will give that a go and let you know how I get on.

_________________
The stats below lie. I'm actually much much worse.

Pro tip: DONT USE EXCEL!

Other than that Howard's solution should work fine.

_________________
SaintK: I'm completely lost