Formula Help,

[JimC]

Hi,
Wonder if anyone can help me.
I have a table of the following information:

No of parts MTBS MTBR CATEGORY
20 08:35:36
22 08:35:36 Same
30 08:47:10 00:11:34 Consecutive Runs
50 08:47:10 Same
90 09:02:35 00:15:25 Consecutive Runs
4 09:02:35 Same
7 09:14:13 00:11:38 Consecutive Runs
9 09:14:13 Same
4 11:16:34 02:02:21 Missing Runs
2 11:16:34 Same
5 11:28:11 00:11:37 Consecutive Runs

MTBS: Mean Time Between Steps
MTBR: Mean Time Between Runs

I am trying to find he number of parts between certain
time limits from the MTBR column into a new matrix like
the following to give me a percentage of parts going
through at certain time limits and also for each of the
categories: i.e. Consecutive Runs, Same, Missing Runs

Time Number of parts
0 to 5 mins 0
5 to 10 mins 0
10 to 20 mins 132

I have tried using an array formula like so:

=SUMIF(D2232,D32="Consecutive Runs",IF(C22:C32,<00:10:00)
(A22:A32))

But I am not to sure how to add in the time limits. I have
also never used array before and am not too sure if the
formula is correct or that I should be using an array. Any
suggestions welcome.

Regards
Jim

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(D2232="Consecutive
runs"),--(C22:C32<=TIME(0,10,0)),A22:A32)

 

[JimC]

Frank,
This works for the small data table that was shown in the
example, when I run the formula into the lager table it
returns a value of #VALUE!
The formula used is
=SUMPRODUCT(--(AM2:AM4382="Consecutive Runs"),--
(AL2:AL4382<=TIME(0,10,0)),AB2:AB4382)
IS there a limit on the number of row that it can caculate?

Jim

 

[Frank Kabel]

Hi
do you have text values in column AL?

 

[JimC]

Yes row 2 & 3 taken them out and works well.
Thank You Very Much.
Jim C