# 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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### 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
229
Replies
8
Views
166
Replies
1
Views
90
Replies
1
Views
42
Replies
1
Views
140

1,127,210
Messages
5,623,407
Members
415,972
Latest member
SY1234

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