# CountIF question

#### zzjasonzz

##### Well-known Member
its been ages since i posted here but I have an excel problem and i know it will take you guys a few seconds to work out, so here it is:

i have one sheet with all the data and then another sheet with a small summary, basically on the data sheet Column A has the persons name and column E has their "Job Type" then column K,L and M have a 1 or a 0 depending, so the data looks roughly like this:

A E K L M

name1 job1 1 0 1
name2 job1 0 1 0
name3 job2 0 1 1
name4 job1 1 0 1
name5 job3 0 0 0
name6 job2 1 1 1
name7 job3 1 0 1
name8 job4 1 0 0
name9 job2 0 1 1

etc

in the summary sheet i want a table to show each jobtype and how many 1's each job has in each column like this:
K L M
job1 2 1 2
job2 1 3 3
job3 1 0 1
job4 0 1 1

I had this so far but obviously doesn't work

=COUNTIF('data'!K3:K65536,'data'!E3=summary!B6)

any ideas would be appreciated, thanks!

Jason

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

##### MrExcel MVP
Book4
ABCDEFGHIJKL
1
2name1job1101Job1212
3name2job1010Job2133
4name3job2011Job3101
5name4job1101Job4100
6name5job3000
7name6job2111
8name7job3101
9name8job4100
10name9job2011
Sheet1

J2, copied across then down:

=SUMPRODUCT(--(\$B\$2:\$B\$10=\$I2),--(C\$2:C\$10=1))

Replies
5
Views
204
Replies
1
Views
284
Replies
5
Views
302
Replies
49
Views
757
Replies
6
Views
340

1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back