how to count values based on column A

[legolas]

Dear All,

I got a large file (sample attached). It got all training courses done in the company according to
date, gender and rank. My task is to cut down the database and create a summary table of how many persons (count only) did the training on given date in column (B) based on course name, how many male and female how many internal and external and how many Civ and MIL attended.

thanks for usual help

[Fotis1991]

You forgot the sample workbook!

[legolas]

sorry did not forget, just had trouble uploading wrong file,
cheers

[SSMANI]

Hi Gentle man,

Please try using Pivot table to get these data. i did as per my understanding have a look at this. Just copy paste in to excel.
Count of Date Column Labels
External External Total Internal Internal Total Grand Total
Row Labels Female Male Female Male
01 April - 25 April 2013 1 1 1 2 3 4
English Course Beginners 1 1 2 2 3
Civ 2 2 2
Mil 1 1 1
French Course Beginners 1 1 1
Mil 1 1 1
04 - 04 July 2013 3 3 2 5 7 10
MS Excel 3 3 2 5 7 10
Civ 3 3 1 4 5 8
Mil 1 1 2 2
08 April - 31 May 2013 2 10 12 12
English Course Beginners 2 6 8 8
Civ 2 5 7 7
Mil 1 1 1
French Course Beginners 4 4 4
Mil 4 4 4
16 - 16 July 2013 1 2 3 5 5 8
17 - 18 July 2013 1 2 3 3
Grand Total 1 6 7 6 24 30 37

[legolas]

@ SSMANI
thanks but can you please put your data in the same file which i uploaded because it did not work when i copy and paste

[Timboson]

Hei Legolas,

Try the COUNTIFS function.

See attached

Regards
Tim

[legolas]

thanks Timboson
worked fine, but is there a way to count blank cells

thanks again

[Timboson]

There's the COUNTBLANK function, which counts blank cells.

You could try heading over to Chandoo.org's website, and pick up a few tips there, http://chandoo.org/excel-formulas/countblank.shtml