Thread: formula to find 2 or more criterias then average the sum
View Single Post
 
Old 02-17-2014, 08:32 PM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
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