Countif based on range in other column

[Besh]

Hi there,

I want to list cell values from different worksheets into the blank column below. These worksheets are named ("Case 1", "Case 2" through to "Case 10"). This cell value in question, can be found in B1 of each of these worksheets. The value that determines what below row they should be listed in is found in B2 of each worksheet.

<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=290><COLGROUP><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><TBODY><TR style="HEIGHT: 24.75pt" height=33><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; WIDTH: 145pt; HEIGHT: 24.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=33 width=193>Scan Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #a5a5a5; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=97>Case #s used for category</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 145pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=193>Adult Follow Up & Original Scans</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 145pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=193>Pediatric Follow Up & Original Scans</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 145pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=193>Total Body Scans</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=xl68> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 145pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=193>Forearm Scans</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=xl68> </TD></TR></TBODY></TABLE>

Codes for each of the scan types found in B2:

"A" Adult Follow Up & Original Scans
"P" Pediatric Follow Up & Original Scans
"TB" Total Body Scans
"F" Forearm Scans

Example Scenario:

Cases (or sheets) 1, 3, 5, and 6 all have an A in cell B2, in that case I would want their case numbers, as displayed in B1, to be listed like so "1, 3, 5, 6" beside "Adult Follow Up & Original Scans".

Cases (or sheets) 2, 4, 7, 9 all have P in cell B2, in that case I would want their case numbers, as displayed in B1 beside "Pediatric Follow Up & Original Scans"

Case (or sheet) 6 has TB in cell B2, in that case I would want their case numbers, as displayed in B1 beside "Total Body Scans"

Cases (or sheets) 8 and 10 have F in cell B2, in that case I would want their case numbers, as displayed in B1 beside "Forearm Scans"