CountIF question

[zzjasonzz]

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

 

[Aladin Akyurek]

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	name1	job1	1	0	1				Job1	2	1	2
3	name2	job1	0	1	0				Job2	1	3	3
4	name3	job2	0	1	1				Job3	1	0	1
5	name4	job1	1	0	1				Job4	1	0	0
6	name5	job3	0	0	0
7	name6	job2	1	1	1
8	name7	job3	1	0	1
9	name8	job4	1	0	0
10	name9	job2	0	1	1
Sheet1

J2, copied across then down:

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

 

[zzjasonzz]

good too see you are still as helpful as always Aladin!