Getting lost in the sumproduct

[leem]

I have been asked to create a staffing chart which can be broken down by a region/service center and then by category groups. I thought it would be simpler to have one chart and by choosing the selections in the drop down boxes, the chart would populate that information. I am having difficulty in making the sumproduct add up because of the different criteria. I'm lost on how to write the formula. I really could use your expertise in helping me to make this happen.

I have attached a sample workbook (Excel 2003) for a better explanation.

For each corresponding year count based on criteria in drop boxes that link to row 2
if a Region is selected from the drop down box which is linked to cell A2 on the charts tab count from Column A on associates tab
If a Service Center is selected from the drop down box which is linked to B2 count Column B on associates tab
If a job category is selected from the drop down box which is linked to C2, look at the 8 lists and pick the right list on the category lists tab and then count column J on associates tab
count column I on associates tab for job status for the selection from the drop down linked to D2 (Full Time, Part Time, All)
for the dates would be as of 12/31/YYYY

based on this criteria, come up with a raw number for Active, Hires, Terms

=SUMPRODUCT(--(associates!$A2:$A$30000=A2),--(associates!$B$2:$B$30000=B3),--ISNUMBER(MATCH(associates!$J$2:$J$30000,'Category Lists'!D2:D8,0)))

My final spreadsheet will have over 30,000 rows on the associates tab so please if you think I am working in the wrong direction with this and you have a much simpler and cleaner way, I would greatly appreciate it.
Thanks
Michelle

[MarvinP]

Hi Michelle,
Your problem looks like a Pivot Chart to me. I'm thinking you could get very close to your current output without any formulas.
Read http://www.mrexcel.com/tip105.shtml for a first example.
or
http://pubs.logicalexpressions.com/P...cle.asp?ID=553

In newer versions of Excel they get easier to create.

[leem]

Thank you, I will try the pivot tables but I'm not familiar with them at all.