Thread: Counting cells with multiple complex criteria
View Single Post
 
Old 12-04-2012, 05:37 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,342
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
On the MessageDetails sheet, try:
=SUMPRODUCT(($B$2:$F$7="X")*(ISNA(MATCH($B$1:$F$1& "."&$A2:$A$7,LookupList,0))))
On any other sheet, the equivalent is:
=SUMPRODUCT((MessageDetails!$B$2:$F$7="X")*(ISNA(M ATCH(MessageDetails!$B$1:$F$1&"."&MessageDetails!$ A$2:$A$7,LookupList,0))))

Note: For some reason, the board's sotware reformats long lines so that, instead of 'MATCH', for example, you may see 'M ATCH'. You'll need to correct any such breaks.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote