# Totaling times of multiple gaps

#### brewben

##### New Member
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?

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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### etaf

##### Well-known Member
AVERAGEIF()
SUMIF()

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

#### brewben

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
you are welcome
Also AVERAGEIFS() which uses multiple criteria

Replies
2
Views
268
Replies
31
Views
653
Replies
8
Views
189
Replies
1
Views
102
Replies
1
Views
39

1,130,191
Messages
5,640,762
Members
417,165
Latest member
Hilders1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back