Ok boys and girls get your head round this one,
3 columns of numbers
a time value (H):mm:ss , a number between 0 & 9 , a time value (H):mm:ss
I need to average the numbers in column 1 where column 2 is a specific number BUT only if the number in column one is NOT 0
This then needs repeating for column 3
I need to work in full columns as this needs repeating for over a thousand rows (the number of rows changes daily)
A demo of the numbers in question
0:38:58 3 196:09:47
0:00:11 9 49:06:03
0:00:02 9 49:01:39
0:00:15 9 48:36:48
2:10:13 6 0:00:00
3:04:03 4 48:19:21
0:33:17 4 48:03:01
51:13:12 4 51:13:12
0:01:38 9 47:55:56
0:03:06 3 46:55:49
0:04:24 4 46:26:37
please help 
Check the help file for exact syntax, but I think the sumif and countif functions are what you’re after. So you can sum or count col A and C depending on B.
Thinking a bit more, might not work unless it allows multiple conditions in the “if” part… if not would have to do in two steps using a temp column.
yeh, I use sumif and countif already, but neither will allow me multiple if’s, I’ve tried breaking it down but can’t seem to get my head arround it.
Array formulas are great for this sort of thing.
Put “=AVERAGE(IF($B$1:$B$32000=n,IF(C$1:C$32000=0,“x”,C$1:C$32000)))” into the formula bar (but substitute ‘n’ for the number you are looking up) and hold down CTRL and SHIFT when you press enter to enter the formula, if you’ve done it right the formula in the bar will have curly braces around it and is now an array formula (you have to repeat the holding down CTRL and SHIFT every time you edit the formula or it will revert to being a normal formula and stop working.)
Array formulas allow you to do some pretty clever stuff but they can be a bit awkward to get your head around. Try here to get you started though:
http://www.mrexcel.com/tip011.shtml
Thanks Phil, I’ll try it from work tomorow and let you know.
YAY, it’s alive !!! Cheers Phil, it works a treat, gave me ideas for improving the whole sheet too
thanks