countif on different sized arrays

[dpowyslybbe]

Hi all,

I'm trying to create a shift performance report at work. Individuals can perform a number of tasks that fall into different categories. I want to be able to work out how many people have been working in a certain category.

Each job and its category is set out as follows:
A B
1 JOB CATEGORY
2 P Picking
3 L Loading
4 SP Picking
5 GI Goods In
6 PPT Loading
7 D-P Picking
8 Mov Movements

The job each individual has done is set out as follows:
AA AB AC
100 Name HOUR 1 TASK HOUR 2 TASK
101 Andy GI GI
102 Bob GI GI
103 Charles P L
104 Dave L L
105 Ed SP Mov
106 Frank D-P P
107 Gayle Mov Mov
108 Harriet P Gi
109 Ian PPT GI
110 Jane P P

My questions is as follows:
- How can I tell how many people were PICKING in hour 1?
NOTE: I want to be able to do this for all hours, and also for all categories, and so I want to try and avoid using helper columns.

I thought about using Countifs with Index/Match, but I'm stumped. Any help would be much appreciated.

Thanks in advance,
Dom

 

[SirJB7]

Hi, dpowyslybbe!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!

 

[dpowyslybbe]

SirJB7,

My apologies. Thank you for pointing me in the right direction.

The attached file hopefully demonstrates what I'm trying to achieve. The yellow cells contains manual answers to what I wish to create formulas for.

Dom

 

[Haseeb A]

Hello dpowyslybbe,

In B24, enter with CTRL+SHIFT+ENTER

=SUM(COUNTIF(B$11:B$20,IF($B$2:$B$8=$A24,$A$2:$A$8)))

Then copy down & across.

 

[dpowyslybbe]

Haseeb, Thank you ever so much.