#1
|
|||
|
|||
formula to find 2 or more criterias then average the sum
I need a formula to do the following look up a name in column A1 on sheet1 then look up that name on sheet2 in column A1:A6 when it finds that name it need to look in column B to find a date, if that date matches January 2014 it has to look up the value in column c and average all the values it finds for that person in January 2014
PLEASE THIS IS URGENT |
#2
|
||||
|
||||
Hi
if this is urgent help us help you by posting a sample sheet ( no pics please) Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
formula to find 2 or more criterias then average the sum
thank you so much - I have attached the sample excel spread sheet
|
#4
|
||||
|
||||
I don't see how values for a particular month can be extracted as values seem to run over several years?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Hi Pecoflyer - the date that it needs to match is the on the End Date on the assessment sheet column F.
If column F on the assessment sheet matches January or say February it has to add the values in column AB on the assessment sheet. |
#6
|
||||
|
||||
You could use:
=INDEX(Assessment!$AB$1:$AB$6,MATCH($A3&H$2,Assess ment!$A$1:$A46&TEXT(Assessment!$F$1:$F6,"MMMM"),0) ) as an array formula. This will output either the amount in AB if a match is found or '#N/A' if not. To display only matches would require: =IF(ISERROR(INDEX(Assessment!$AB$1:$AB$6,MATCH($A3 &H$2,Assessment!$A$1:$A46&TEXT(Assessment!$F$1:$F6 ,"MMMM"),0))),"",INDEX(Assessment!$AB$1:$AB$6,MATC H($A3&H$2,Assessment!$A$1:$A46&TEXT(Assessment!$F$ 1:$F6,"MMMM"),0))) again, as an array formula. Array formulae are input with Ctrl-Shift-Enter.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Paul you a superstar - Thank you it works. Many thank
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge to print specific record based on mergefield criterias | nicnad | Mail Merge | 1 | 02-22-2012 01:53 AM |
Excel 2010 formula rounds - arithmetic average calculation | YooNaa Kim | Excel | 1 | 06-07-2011 08:50 PM |
'AVERAGE' Formula | nfphilpot | Excel | 3 | 11-24-2010 02:19 PM |
Find duplicates formula | hannu | Excel | 2 | 10-26-2010 02:48 AM |
Needs help to find excel formula- Please | aamer_1983 | Excel | 2 | 07-13-2009 01:46 AM |