Help with Analysis

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

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
 
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=criteria_2)*(C1:C1000=crite
ria_3))
to sum a range based on multiple criteria use
=SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criteria_2)*(C1:C1000=crite
ria_3),D1:D1000) -> sums columns D

HTH
Frank
 
Back
Top