Totaling times of multiple gaps

[brewben]

Hi,


In my sheet (example below) it's easy to add up and total the average time overall (including the gaps) spent on each model but I would like to find the average time of the recorded GAPs.

I can index/match the SCAN column to find "GAP", and it's relevant time in TOTAL TIME but putting SUMIFS (and then the AVERAGE) at the start, only returns the final value (ie. row 8) - it doesn't add up/average just the times relevant to GAP.

Is it possible to do this?

Thanks in advance.

Brewster.

NO.	TIME IN	TIME OUT	TOTAL TIME	scan	MODEL
1	07:06:39	07:23:15	0:16:36	6576080	12
2	09:03:51	10:00:01	0:56:10	4285151	23243
3	10:05:16	10:28:34	0:23:18	6585037	362
4	10:33:53	10:36:20	0:02:27	6276249	4356
5	10:53:56	11:23:23	0:29:28	GAP	3654
6	11:47:29	12:01:30	0:14:01	6285203	5678
7	13:11:17	13:14:24	0:03:07	7485176	5876
8	13:22:21	13:33:17	0:10:56	GAP
9	13:33:34	13:52:39	0:19:05	7285417	5768

 

[etaf]

AVERAGEIF()
SUMIF()

SUMIF( RANGE of scan, "GAP", range of totals)

 

[brewben]

AVERAGEIF()
SUMIF()

SUMIF( RANGE of scan, "GAP", range of totals)

Thank you for your speedy response:

I don't think i've understood the Averageif function correctly..?

=AVERAGE((SUMIF(E6:E30,"gap",D6:D30)))

Apologies for my ignorance.

Brewster

 

[etaf]

SUMIF() and AVERAGEIF() are 2 different functions
so if you want to sumup the times use SUMIF()
or if you want an Average then use AVERAGEIF()
AVERAGEIF( RANGE of scan, "GAP", range of totals)

 

[brewben]

SUMIF() and AVERAGEIF() are 2 different functions
so if you want to sumup the times use SUMIF()
or if you want an Average then use AVERAGEIF()
AVERAGEIF( RANGE of scan, "GAP", range of totals)

Many thanks. Did not know that Average could be used like that!

 

[etaf]

you are welcome
Also AVERAGEIFS() which uses multiple criteria