Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2014, 06:15 AM
samtitus samtitus is offline formula to find 2 or more criterias then average the sum Windows XP formula to find 2 or more criterias then average the sum Office 2007
Novice
formula to find 2 or more criterias then average the sum
 
Join Date: Feb 2014
Location: Johannesburg, South Africa
Posts: 4
samtitus is on a distinguished road
Angry 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
Reply With Quote
  #2  
Old 02-11-2014, 12:35 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline formula to find 2 or more criterias then average the sum Windows 7 64bit formula to find 2 or more criterias then average the sum Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 02-11-2014, 11:26 PM
samtitus samtitus is offline formula to find 2 or more criterias then average the sum Windows XP formula to find 2 or more criterias then average the sum Office 2007
Novice
formula to find 2 or more criterias then average the sum
 
Join Date: Feb 2014
Location: Johannesburg, South Africa
Posts: 4
samtitus is on a distinguished road
Default formula to find 2 or more criterias then average the sum

thank you so much - I have attached the sample excel spread sheet
Attached Files
File Type: xlsx appraisal control sheet 1.xlsx (15.7 KB, 14 views)
Reply With Quote
  #4  
Old 02-14-2014, 10:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline formula to find 2 or more criterias then average the sum Windows 7 64bit formula to find 2 or more criterias then average the sum Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 02-16-2014, 09:06 PM
samtitus samtitus is offline formula to find 2 or more criterias then average the sum Windows XP formula to find 2 or more criterias then average the sum Office 2007
Novice
formula to find 2 or more criterias then average the sum
 
Join Date: Feb 2014
Location: Johannesburg, South Africa
Posts: 4
samtitus is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 02-17-2014, 08:32 PM
macropod's Avatar
macropod macropod is offline formula to find 2 or more criterias then average the sum Windows 7 32bit formula to find 2 or more criterias then average the sum Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #7  
Old 02-17-2014, 10:16 PM
samtitus samtitus is offline formula to find 2 or more criterias then average the sum Windows XP formula to find 2 or more criterias then average the sum Office 2007
Novice
formula to find 2 or more criterias then average the sum
 
Join Date: Feb 2014
Location: Johannesburg, South Africa
Posts: 4
samtitus is on a distinguished road
Default

Paul you a superstar - Thank you it works. Many thank
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to find 2 or more criterias then average the sum Mail Merge to print specific record based on mergefield criterias nicnad Mail Merge 1 02-22-2012 01:53 AM
formula to find 2 or more criterias then average the sum 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:36 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft