HELP! Finding SUM, AVERAGE, MIN, MAX formulas for cells that have numbers and text in them

[mathkkad]

Hi,

Could you please help me finding excel formulas for the following problem?

I have the following cells on the same column (column A, rows 1-5):

A
1 00:30 random1 !day random6
2 01:00 random2 !day
3 02:00 random3 !night
4 01:15 random4 !night random7
5 00:15 random5 !day

Basically all cells contain: an hour (always at the beginning of the cell), random words, a key word (that starts with !)

First for !day

Could you help me SUM all the hours in the cells that have the keyword !day in it in one formula? (without splitting the cell data in multiple cells) Should be 01:45
Could you help me AVERAGE all the hours in the cells that have the keyword !day in it in one formula? (without splitting the cell data in multiple cells) Should be 00:35
Could you help me MIN all the hours in the cells that have the keyword !day in it in one formula? (without splitting the cell data in multiple cells) Should be 00:15
Could you help me MAX all the hours in the cells that have the keyword !day in it in one formula? (without splitting the cell data in multiple cells) Should be 01:00

also do the same for !night

Could you help me SUM all the hours in the cells that have the keyword !night in it in one formula? (without splitting the cell data in multiple cells) Should be 03:15
Could you help me AVERAGE all the hours in the cells that have the keyword !night in it in one formula? (without splitting the cell data in multiple cells) Should be 01:37
Could you help me MIN all the hours in the cells that have the keyword !night in it in one formula? (without splitting the cell data in multiple cells) Should be 01:15
Could you help me MAX all the hours in the cells that have the keyword !night in it in one formula? (without splitting the cell data in multiple cells) Should be 02:00

Thank you very much!

 

[sheetspread]

Excel 2010
	A
1	00:30 random1 !day random6
2	01:00 random2 !day
3	02:00 random3 !night
4	01:15 random4 !night random7
5	00:15 random5 !day
6
7	01:45
8	00:35
9	00:15
10	01:00
11
12
13	03:15
14	01:37
15	01:15
16	02:00
Sheet4

Cell Formulas
Range	Formula
A7	{=TEXT(SUM(IF(ISNUMBER(SEARCH("!day",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A8	{=TEXT(AVERAGE(IF(ISNUMBER(SEARCH("!day",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A9	{=TEXT(MIN(IF(ISNUMBER(SEARCH("!day",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A10	{=TEXT(MAX(IF(ISNUMBER(SEARCH("!day",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A13	{=TEXT(SUM(IF(ISNUMBER(SEARCH("!night",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A14	{=TEXT(AVERAGE(IF(ISNUMBER(SEARCH("!night",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A15	{=TEXT(MIN(IF(ISNUMBER(SEARCH("!night",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
A16	{=TEXT(MAX(IF(ISNUMBER(SEARCH("!night",$A$1:$A$5)),TIMEVALUE(LEFT($A$1:$A$5,5)))),"hh:mm")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mathkkad]

Thank you very much sheetspread. It works perfectly! Have a great day