CountIf / SumIf / ???

F

Fister

New Member
Joined
Jul 25, 2011
Messages
12
I'm pulling pivot tables from a worksheet with 111,098 rows and 31 columns of data. I'm trying to get a count based on this formula:

=COUNTIFS($B$2:$B$111098,B2,$A$2:$A$111098,"<>"&A2)>0

This formula give me "True" or "False" if a guest number (column B) has visited more than one property (column A). Based on this, I'd like another column that counts the guest numbers once if it satisfies the above criteria.

Possible?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
<table border="0" cellpadding="0" cellspacing="0" width="272"><colgroup><col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:52pt" height="20" width="69">Property</td> <td class="xl68" style="width:44pt" width="59">Guest #</td> <td class="xl69" style="width:57pt" width="76">C-P</td> <td class="xl67" style="width:51pt" width="68">C-P Count</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Kal Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Kal Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">Kal Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">100858</td> <td class="xl70" align="center">TRUE</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">102996</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">SD Resort</td> <td class="xl66">105255</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">SD Resort</td> <td class="xl66">107341</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">117323</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">LQ Lodge</td> <td class="xl66">124381</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">SD Resort</td> <td class="xl66">129531</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">1
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">SD Resort</td> <td class="xl66">129531</td> <td class="xl70" align="center">FALSE</td> <td class="xl66">
</td> </tr> </tbody></table>
 
Upvote 0
Maybe this:

Note: VERDADEIRO = TRUE and FALSO = FALSE.

Excel 2007
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Property</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Guest #</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">C-P</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">C-P Count</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Kal Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">Kal Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Kal Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">100858</TD><TD style="TEXT-ALIGN: center">TRUE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">102996</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">SD Resort</TD><TD style="TEXT-ALIGN: center">105255</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">SD Resort</TD><TD style="TEXT-ALIGN: center">107341</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">117323</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">LQ Lodge</TD><TD style="TEXT-ALIGN: center">124381</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">SD Resort</TD><TD style="TEXT-ALIGN: center">129531</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">SD Resort</TD><TD style="TEXT-ALIGN: center">129531</TD><TD style="TEXT-ALIGN: center">FALSE</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">FALSO</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD></TR></TBODY></TABLE>
Sheet3


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">=COUNTIFS($B$2:$B$16,B2,$A$2:$A$16,"<>"&A2)>0</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIFS($B$2:$B$16,B2,$A$2:$A$16,"<>"&A2)>0,IF(B1<>B2,1,""),0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
Fister said:
<TABLE border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 52pt; HEIGHT: 15pt" class=xl68 height=20 width=69>Property</TD><TD style="WIDTH: 44pt" class=xl68 width=59>Guest #</TD><TD style="WIDTH: 57pt" class=xl69 width=76>C-P</TD><TD style="WIDTH: 51pt" class=xl67 width=68>C-P Count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>Kal Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>Kal Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>Kal Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>100858</TD><TD class=xl70 align=center>TRUE</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>102996</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>SD Resort</TD><TD class=xl66>105255</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>SD Resort</TD><TD class=xl66>107341</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>117323</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>LQ Lodge</TD><TD class=xl66>124381</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>SD Resort</TD><TD class=xl66>129531</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>1

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>SD Resort</TD><TD class=xl66>129531</TD><TD class=xl70 align=center>FALSE</TD><TD class=xl66>

</TD></TR></TBODY></TABLE>
Click to expand...
<TABLE style="WIDTH: 295pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=394><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3441" width=97><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3384" width=95><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3328" width=94><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3840" width=108><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>Property</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95>Guest #</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=94>C-P</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=108>C-P Count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>Kal Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>Kal Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>Kal Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>100858</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>TRUE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>102996</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>SD Resort</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>105255</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>SD Resort</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>107341</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>117323</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>LQ Lodge</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>124381</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>SD Resort</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>129531</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 73pt; HEIGHT: 15pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 height=20 width=97>SD Resort</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 71pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl63 width=95 align=right>129531</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=94>FALSE</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #efefef; WIDTH: 81pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl64 width=108>0</TD></TR></TBODY></TABLE>

C2, just enter and copy down:
Rich (BB code): 
=COUNTIFS($B$2:$B$16,B2,$A$2:$A$16,"<>"&A2)>0
This is the formula you provided yourself.

D2, just enter and copy down:
Rich (BB code): 
=IF(C2,IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),0,1),0)
It looks like you require something like the foregoing: Mark as 1 if a guest
visits two or more distinct properties. You probably want to sum the D-range
in order to determine the number of guests visiting multiple distinct properties.

Another option, given the huge range you want to examine, is to create a
unique list of guests in a separate area, say in F:G, using e.g. Advanced Filter
like below:

<TABLE style="WIDTH: 120pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=160><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2389" width=67><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 width=67>Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 70pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=93>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67>Guest #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 70pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=93>>1 Property</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>100858</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>102996</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>105255</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>107341</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>117323</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>124381</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=67 align=right>129531</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR></TBODY></TABLE>

G1, just enter:
Rich (BB code): 
=SUM(G3:G9)

G3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=(SUM(IF(FREQUENCY(IF($A$2:$A$16<>"",IF($B$2:$B$16=F3,
  MATCH("~"&$A$2:$A$16,$A$2:$A$16&"",0))), 
   ROW($B$2:$B$16)-ROW($B$2)+1),1))>1)+0
 
Upvote 0
A small modification in my formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Property</td><td style="font-weight: bold;text-align: center;;">Guest #</td><td style="font-weight: bold;text-align: center;;">C-P</td><td style="font-weight: bold;text-align: center;;">C-P Count</td><td style="font-weight: bold;text-align: center;;">Result1</td><td style="font-weight: bold;text-align: center;;">Result2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Kal Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Kal Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Kal Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">100858</td><td style="text-align: center;;">TRUE</td><td style="text-align: center;;"></td><td style="text-align: center;;">VERDADEIRO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">102996</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">SD Resort</td><td style="text-align: center;;">105255</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">SD Resort</td><td style="text-align: center;;">107341</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">117323</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">LQ Lodge</td><td style="text-align: center;;">124381</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">0</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">SD Resort</td><td style="text-align: center;;">129531</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">SD Resort</td><td style="text-align: center;;">129531</td><td style="text-align: center;;">FALSE</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$B$2:$B$16,B2,$A$2:$A$16,"<>"&A2</font>)>0</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">$B$2:$B$16,B2,$A$2:$A$16,"<>"&A2</font>),IF(<font color="Red">B1<>B2,1,""</font>),IF(<font color="Red">B1<>B2,0,""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Thank you for your kind help, Aladin and Mark!
I'll try both formulas and let you know if they produce what I need them to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
COUNTIFS
Replies
6
Views
229
carlclements
C
T
  • Solved
Integer programming with binary variables in Solver - issue with constraint?
Replies
3
Views
376
StephenCrump
S
B
  • Solved
Use cell text in COUNTIFS formula referring to a Table
Replies
4
Views
474
Fluff
Fluff
L
  • Solved
Number series based on multiple criteria
Replies
3
Views
207
SanjayGMusafir
SanjayGMusafir
R
  • Question Question
Raw Data Filter
Replies
1
Views
81
RobP
R

Forum statistics

Threads
1,224,587
Messages
6,179,740
Members
452,940
Latest member
Lawrenceiow

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top