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

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!

Try a user defined formula like this.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter =SumSpecial(A1:A5,"!day")

Remember to save the workbook as a macro enabled workbook .xlsm

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

To change a Title go to your first post, click EDIT then Go Advanced and change your title

Thank you for the formula Phuocam, it works very well.

Do you think you could change the RIGHT($A$1:$A$5,3)="day" to a improved "search the cell for the exact word matching - making its position arbitrary" as not all keywords at the end of the cell have the same size and sometimes the keyword is not the final word of the cell. (I could also use a separate cell like B1 for the "day" keyword and i can make a reference to that for a search) - i have modified my first post accordingly
Plus in reality i have a lot of cells that i have to manually modify if i have to manually adjust the keyword and the number of ending letters for the RIGHT formula.

Do you think it is a better way to put that part of the formula? As the rest works very well. Thank you again