Thread: Counting cells with multiple complex criteria
View Single Post
 
Old 12-05-2012, 04:58 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit 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
Hi TishyMouse,

I think there's something wrong with your conditional format rules on the Data sheet.

It seems to me that the intent is to highlight those cells that don't have an 'X' match on the MessageDetails sheet, but that's not what's happening. However, if you correlate the highlights on the Data sheet with the values on the MessageDetails sheet, you'll see that the highlighting is applied to some cells for which there is an 'X' match on the MessageDetails sheet. Indeed, three of the four highlighted cells have an 'X' match on the MessageDetails sheet and one that doesn't have an 'X' match on the MessageDetails sheet isn't highlighted.

Assuming I'm correct about the above, the conditional format formula should be:
=OFFSET(MessageDetails!$A$1,MATCH($A2,MessageDetai lsSourceMessages,0),MATCH($B2,MessageDetailsColHea dings,0))=0
and the count of unmatched entries on the Data sheet should be 2, which you can get via:
=SUMPRODUCT((TableMessageDetails[[COL1]:[COL5]]=0)*NOT(ISNA(MATCH(TableMessageDetails[Source Message]&"."&TableMessageDetails[[#Headers],[COL1]:[COL5]],TableData[Row.Col],0))))

Note: As before, watch for unwanted spaces inserted by the board software.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote