elp with counting the number of rows matching 2 criteria please

[HBK]

Okay so I have the following information below and I want to count the number of Green, Blue, Amber and Red scores that each risk ref has. But I don’t want to use a pivot table since it has to be automatically updated every time the source data is changed. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> <TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse; mso-padding-bottom-alt: 0cm; mso-padding-left-alt: 0cm; mso-padding-right-alt: 0cm; mso-padding-top-alt: 0cm" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes" height=20><TD class=xl39 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Risk ref</TD><TD class=xl39 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Score</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl42 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: green">Green</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl42 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: green">Green</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>CapMan2</TD><TD class=xl43 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">Amber</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 21" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Claim1</TD><TD class=xl43 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">Amber</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 22" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Claim1</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 23; mso-yfti-lastrow: yes" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Claim1</TD><TD class=xl41 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">Blue</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21>Claim2</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: green">Green</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 45" height=21><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21>Claim2</TD><TD class=xl45 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: green">Green</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 46" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Claim2</TD><TD class=xl43 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">Amber</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 47; mso-yfti-lastrow: yes" height=20><TD class=xl40 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Claim2</TD><TD class=xl44 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: green">Green</TD></TR></TBODY></TABLE></o>
<o> </o>
So I’d like a formula that works out how many rows have the risk reference Claim2 and the score Green.<o></o>
<o> </o>
And the next would be how many rows have the risk reference Claim2 and the score Blue and so on and so on.

Any help would be much appreciated. Thanks for your time.

James

<o></o>

 

[VoG]

For example

=SUMPRODUCT(--(A2:A15="CapMan2"),--(B2:B15="Blue"))

 

[HBK]

I'm not sure whether this is important but I'm using Excel 2003 and so I don't think I can use the COUNTIFS function.

 

[HBK]

Thanks a lot VoG that formula works perfectly.

Much appreciated.