Sum If problem?

[JonnyDijksman]

I have a problem that has been boggling my brain for the past 2 hours and hopefully you guys can help.

I have a column of errors, the employee with whom it concerns, the cost centre to which that employee is attached and then a number of columns for different types of errors (overtime, travel time, basic pay etc) which contain only a '1' if that is the relevant error type for that particular query. At the top of each error type is a '=sum()' cell to show the total queries of that type.


I hope your brains haven't exploded already


Now, I need to (on another sheet) create a breakdown of the top 3 error type totals (lets say ot, callout and basic), and their coinciding cost centre breakdown.


https://docs.google.com/spreadsheet/ccc?key=0AgOpAwUBu4T_dHE1WlVHWjlhLXZnZEJIbjc1X1dhbVE#gid=0


From this rough data, I want to be able to 'sum' all the '1s' in the OT column if that row has a particular cost centre, so i am left with the total OT errors and what cost centres (and their tally) make up that total.


I will give a particularly enthusiastic Hi-5 to anyone that can help me with this


Kind Regards

 

[Faseeh]

Hi JonnyDijksman,


For the resulting like below present in between cells H4:K10 like bellow:

[pre]

Cost Center	OT	Basic	Callout
London	        5	4	5
Scotland	4	1	1
Wales	        0	2	1
Ireland	        1	1	1
Leeds	        1	1	1
Manchester	1	0	1[/pre]
Enter this formula and drag to right/down:


=SUMPRODUCT(($A$3:$A$20=$H5)*($C$2:$E$2=I$4),$C$3:$E$20)

Regards,

Faseeh