Totaling times of multiple gaps

brewben

New Member
Joined
Nov 5, 2015
Messages
49
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 INTIME OUTTOTAL TIMEscanMODEL
107:06:3907:23:150:16:36657608012
209:03:5110:00:010:56:10428515123243
310:05:1610:28:340:23:186585037362
410:33:5310:36:200:02:2762762494356
510:53:5611:23:230:29:28GAP3654
611:47:2912:01:300:14:0162852035678
713:11:1713:14:240:03:0774851765876
813:22:2113:33:170:10:56GAP
913:33:3413:52:390:19:0572854175768
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,671
Office Version
  1. 365
Platform
  1. MacOS
AVERAGEIF()
SUMIF()

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

brewben

New Member
Joined
Nov 5, 2015
Messages
49
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
Joined
Oct 24, 2012
Messages
4,671
Office Version
  1. 365
Platform
  1. MacOS
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)
 
Solution

brewben

New Member
Joined
Nov 5, 2015
Messages
49
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
Joined
Oct 24, 2012
Messages
4,671
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
Also AVERAGEIFS() which uses multiple criteria
 

Watch MrExcel Video

Forum statistics

Threads
1,129,840
Messages
5,638,656
Members
417,041
Latest member
Molo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top