MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by tessa gazzard on November 30, 2001 5:50 PM

right how to best explain i have a list on names in A1 sheet1 about40 entery most names appear more then once some four or five times in the h coloum i have time spent on line for each name. On sheet 2 i have the names again in the A coloum but only one occourance of each name. next i want a colooum with a formuler that will look at the name in A1 sheet 2 say the name was ken jones then i want to be able to go to sheet1 and look for each occourance of that name in the A coloum each time it appears i want excel to average all the corrosponing time online which is displayed in min in coloum H so i end up with average time online for each of the names in sheet 2 coloum A i kind of need a formuler that will average all number in coloum H that corrospond to meeting a certain name
a h

ken 10mim
jane 25min
bert 4min
ken 20 min
bert 6min
tess 35min
ken 40min

jane average time online
bert average time online
ken average time online
tess average time online
something like this hope you can help i tried with no luck so far

Posted by Russell Hauf on November 30, 2001 6:09 PM

Try this:


This assumes that your names on Sheet1 are in cells A1 to A40, and that the minutes are in column B (again to B40), and that the names in sheet2 are in column A also. Enter the above formula in cell B1 (assumes first name is in A1 on sheet2).

Make sense? Hope so!


Posted by Aladin Akyurek on December 02, 2001 4:16 AM

Getting curious...

> something like this hope you can help i tried with no luck so far

Tessa --

What is wrong? You posted this message much earlier to which I replied (even IML got in the loop). I gave you a formula off-line, which is exactly the same as what Russell Hauf formulates below.

Just curious, that's all.



Posted by tessa gazzard on December 03, 2001 2:47 PM

Re: Getting curious...

yes i know you formuler worked great the sumproduct but what i wanted was to know how to do the same thing but avenging the numbers in the coloum this time were before i had to add the numbers that matched the two criteria sorry to be a nusicence acually i aint going to bother with that any more but i would like to know i am picking up so much i spend most of my time here reading everyone post and i just got some great excel books so im on my way no i doing a course and this is for my course work

Posted by Arum Pitt on December 04, 2001 3:10 AM

Could you re-post an English translation, please ?