Help with Analysis
 

I have a large worksheet that I need to analyze a multiple set of
conditions, and return a count based on the analysis. I have exhausted
my knowledge on how to accomplish this. I have tried variations of
COUNTIF and Arrays, with no luck. A PIVOTTABLE works but multiple
people with no knowledge of Excel view the results and do not know how
to manipulate the data. So I have opted to report the results in table
format with a worksheet for each month. What I need is:

How many people in October, assigned to a certain team, peroformed an
action, within a specific time frame.

There are 12 months, 6 teams, 13 actions, 2 time frames that must be
analyzed with the data coming from one worksheet.

The results are reported as a worksheet for each month, actions as
rows, and teams as merged columns with time frames as the sub-columns.

Any help would be greatly appreciated. I would like to do it as a
formula vice VBA, since my knowledge of VBA is weak.

Thank you,
Randy

Help with Analysis
 

Hi Randy
if pivot tables work I would try to train your colleagues :-)
But if you have to use formulas maybe using SUMPRODUCT could help you.
e.g. use the following for counting based on multiple criteria:
=SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criter ia_2)*(C1:C1000=crite
ria_3))
to sum a range based on multiple criteria use
=SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criter ia_2)*(C1:C1000=crite
ria_3),D1:D1000) - sums columns D

HTH
Frank

Randy wrote:
I have a large worksheet that I need to analyze a multiple set of
conditions, and return a count based on the analysis. I have
exhausted
my knowledge on how to accomplish this. I have tried variations of
COUNTIF and Arrays, with no luck. A PIVOTTABLE works but multiple
people with no knowledge of Excel view the results and do not know
how
to manipulate the data. So I have opted to report the results in
table
format with a worksheet for each month. What I need is:

How many people in October, assigned to a certain team, peroformed an
action, within a specific time frame.

There are 12 months, 6 teams, 13 actions, 2 time frames that must be
analyzed with the data coming from one worksheet.

The results are reported as a worksheet for each month, actions as
rows, and teams as merged columns with time frames as the
sub-columns.

Any help would be greatly appreciated. I would like to do it as a
formula vice VBA, since my knowledge of VBA is weak.

Thank you,
Randy