Advanced COUNTIF and SUMIF

[ManinOZ]

Hello, I have already gotten assistance on this workbook but now i thought I would just post the whole workbook and try to get it finished.

At the top I need the various formulas, below is an example data set.

Background.
This sales crew works 16 shifts per month.
Each shift the crew is divided into teams of 2 or 3 and assigned one of 5 areas (T,B,Q,W or O)
Each salesperson can make either Small sales or Large sales
Each sales person will only appear once for each shift, ,if there is a double that is my data entry error.
Each sales team will be deployed to one area per shift.
Small sales values may be from 0-30
Large sales values may be from 0-20


Want formulas for the following columns
Q:Q - U:U, count how many times each sales person is assigned to each area in the month.
V:V - W:W, sum of total Small and Large Sales for each sales person for the month.
X:X - AB:AB, Sum of Small sales each sales person gets in each sales area.
AC:AC- AG:AG, Sum of Large sales each sales person gets in each sales area.

Anymore info let me know.

[Pete_UK]

Put this in Q3:

=COUNTIFS($L$21:$L$148,LEFT(Q$2),$C$21:$C$148,$A3)+COUNTIFS($L$21:$L$148,LEFT(Q$2),$F$21:$F$148,$A3)+COUNTIFS($L$21:$L$148,LEFT(Q$2),$I$21:$I$148,$A3)

Copy across to U3, then down. Put this in V3:

=SUMIF($C$21:$C$148,$A3,D$21:D$148)+SUMIF($F$21:$F$148,$A3,G$21:G$148)+SUMIF($I$21:$I$148,$A3,J$21:J$148)

Copy into W3, then down. Put this in X3:

=SUMIFS($D$21:$D$148,$C$21:$C$148,$A3,$L$21:$L$148,LEFT(X$2))+SUMIFS($G$21:$G$148,$F$21:$F$148,$A3,$L$21:$L$148,LEFT(X$2))+SUMIFS($J$21:$J$148,$I$21:$I$148,$A3,$L$21:$L$148,LEFT(X$2))

Copy across to AB3, then down. Put this in AC3:

=SUMIFS($E$21:$E$148,$C$21:$C$148,$A3,$L$21:$L$148,LEFT(AC$2))+SUMIFS($H$21:$H$148,$F$21:$F$148,$A3,$L$21:$L$148,LEFT(AC$2))+SUMIFS($K$21:$K$148,$I$21:$I$148,$A3,$L$21:$L$148,LEFT(AC$2))

Copy across to AG3, then down.

Hope this helps.

Pete