JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Need a count and then match formula
Have two main worksheets with data. Column I on loss worksheet contains what I need to match and count in column D of dispute worksheet.
I need this to populate on an entirely different worksheet called Graphs.
What I ultimately want to do is that every time a # is sheet dispute, I want the formula to just populate the # of matches in a cell on the graphs tab.
So, for example: Loss ID 7569 is also on the Dispute ID sheet column. That would count as 1 and I want the formula to automatically show this.
I have about 30000 rows (or maxed out).
What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Graph worksheet formula:
=countif(Dispute!D:D,Loss!I1)
Hmm. It returned a value! error.
Ok I just fixed that error. But it shows 0 now and I know that there are some same ID's. Now I need to throw in another piece I suppose.
On the loss and dispute ID's, there is a specific locator # in column N. That locator # is what I want to reference to then specifically count if an ID matches to that locator. Example:
Locator 123456 with ID 7549 does appear on the dispute tab and then also on the loss tab.
Locator 567890 with ID 6543 does appear on the dispute tab and then also on the loss tab.
I need a formula next to each locator that does this same count/ match.
I'm not real clear on how you have this set up, but this is what I think you've said & how I would approach it IF you're using Excel 2007 (countifs function won't work on older Excel versions)
<TABLE style="WIDTH: 304pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=402 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 5290" width=124><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1877" width=44><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 938" width=22><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 896" span=5 width=21><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 896" width=21><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 896" span=2 width=21><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1877" width=44><TBODY><TR style="HEIGHT: 45pt" height=60><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 93pt; BORDER-BOTTOM: #ffffff; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" width=124 height=60>Dispute Sheet
Col C </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 33pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=44>Dispute Sheet
Col D
ID </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 17pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=22>Col
E </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
F </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
G </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
H </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
I </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
J </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
K </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
L </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 16pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=21>Col
M </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 33pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=44>Dispute Sheet
Col N
Locator </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 2 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 3 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>6543 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>567890 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 4 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 5 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 6 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 7 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 8 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>6543 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>567890 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Dispute Sheet Row 9 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>6543 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>567890 </TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 200pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=264 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 4224" width=99><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1450" width=34><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 938" span=4 width=22><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1834" width=43><TBODY><TR style="HEIGHT: 45pt" height=60><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 74pt; BORDER-BOTTOM: #ffffff; HEIGHT: 45pt; BACKGROUND-COLOR: transparent" width=99 height=60>Loss Sheet
Col H </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 26pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=34>Loss Sheet
Col I
ID </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 17pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=22>Col
J </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 17pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=22>Col
K </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 17pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=22>Col
L </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 17pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=22>Col
M </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 32pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=43>Loss Sheet
Col N
Locator </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Loss Sheet Row 2 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>7569 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>123456 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Loss Sheet Row 3 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>6543 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>567890 </TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 394pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=524 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 5034" width=118><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 2133" width=50><COL style="WIDTH: 267pt; mso-width-source: userset; mso-width-alt: 15189" width=356><TBODY><TR style="HEIGHT: 33.75pt" height=45><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 89pt; BORDER-BOTTOM: #ffffff; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=118 height=45>Graph Sheet
Col A </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 38pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=50>Graph Sheet
Col B </TD><TD class=xl68 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 267pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=356>Formula in col B </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 2 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">5 </TD><TD class=xl68 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">=COUNTIFS(Dispute!$D$2:$D$15,Loss!I2,Dispute!$N$2:$N$15,Loss!N2) </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 2 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">3 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
if you don't have Excel 2007 you could use the 2nd formula shown:
<TABLE style="WIDTH: 423pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=563 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 5034" width=118><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 2133" width=50><COL style="WIDTH: 296pt; mso-width-source: userset; mso-width-alt: 16853" width=395><TBODY><TR style="HEIGHT: 33.75pt" height=45><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 89pt; BORDER-BOTTOM: #ffffff; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=118 height=45>Graph Sheet
Col A </TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 38pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=50>Graph Sheet
Col B </TD><TD class=xl68 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 296pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=395>Formula in col B </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 2 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">5 </TD><TD class=xl68 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">=COUNTIFS(Dispute!$D$2:$D$15,Loss!I2,Dispute!$N$2:$N$15,Loss!N2) </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 3 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">3 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 4 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">5 </TD><TD class=xl68 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">=SUMPRODUCT((Dispute!$D$2:$D$15=Loss!I2)*(Dispute!$N$2:$N$15=Loss!N2)) </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Graph Sheet Row 5 </TD><TD class=xl67 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">3 </TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Thanks. I just saw this and I did the second formula. It returned 0 so I'm not sure it worked because I entered two same #'s to try it and nothing. I did try your second formula since I have excel 2003. I don't see where the locator is named to lookup for the formula?
Tab 1:
# of times on both tabs
Shelly Should be 3
Matt Should be 1
Tab 2 "Loss"
Locator ID
Shelly 7549
Matt 1234
Matt 5678
Shelly 546788
Shelly 123456
Tab 3 "Dispute"
Locator ID
Shelly 7549
Matt 1234
Matt 3458
Shelly 546788
Shelly 123456
=SUMPRODUCT((Dispute!$D$2:$D$15 =Loss!i 2 this is saying all the IDs in column on "Dispute" sheet that match the ID value in "Loss" sheet cell i2 )*(Dispute!$N$2:$N$15 =Loss!N 2 this is saying all the LOCATORs in "Dispute" sheet that match the LOCATOR value in "Loss" sheet cell N2))
Yes this is not working for me. I must have done something wrong. When you say I2 and N 2 are the locators for those tabs, I don't want this to be where the locator is. Anyhow, I'm giving up on this for now. But thanks for the help!
Threads
1,214,510
Messages
6,119,933
Members
448,932
Latest member
mile5165