Count unique if adjacent cells are not blank

I

I'm rubbish with Excel

Board Regular
Joined
Nov 18, 2003
Messages
52
Hi,

I've been looking at this for a while now, and I've looked as some similar solutions, but I can't get my head around this:

I've got a spreadsheet similar to the one below(which will expand over time - although that's not necessarily an issue now)

Col A____________Col B____________________Col C
Staff name_______Qualification attained_______Exams currently studying
W________________________________________EX1
W_______________QU1
W________________________________________EX2
X_______________QU1
X_______________QU2
X_______________QU3
X________________________________________EX2
X________________________________________EX3
Y________________________________________EX1
Z_______________QU4


I need to be able to count the following things:

- No. of staff that have qualifications
- No. of staff that currently studying for an exam
- No. of staff that have qualifications, that are also currently studying for an exam
- No. of staff that have qualifications, that not currently studying for an exam

The problem I'm having is pulling the unique values from each column.

Any help would be greatly appreciated.

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Try some of these.

Excel Workbook
ABCDEFGHI
1Staff NameQualication AttaindExam Currently StudyingQualifcationTotalsExam Currently StudyingTotals
2BillEX1QU13EX13
3SaraQU1QU22EX21
4HelenQU2EX1
5JohnEX2
6AllanQU1Total Qualifications5
7JackQU2EX1Total Exam Currently Studying4
8GrahamQU1
Sheet5
Click to expand...
 
Upvote 0
Maybe this:

Note: in the range L2:O2 I used array formulas - entered with Ctrl+Shift+Enter and not only Enter.

<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 /><col /><col /><col /><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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">Staff name</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">Qualification aattained</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">Exams currently studying</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">2007-></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Qual</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Exam</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Qual & Exam</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Qual not Exam</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #92D050;;"><-2003</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Qual</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Exam</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Qual & Exam</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Qual not Exam</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">W</td><td style="text-align: center;;"></td><td style="text-align: center;;">EX1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">W</td><td style="text-align: center;;">QU1</td><td style="text-align: center;;"></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><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;">4</td><td style="text-align: center;;">W</td><td style="text-align: center;;"></td><td style="text-align: center;;">EX2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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: 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;">5</td><td style="text-align: center;;">X</td><td style="text-align: center;;">QU1</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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: 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;">6</td><td style="text-align: center;;">X</td><td style="text-align: center;;">QU2</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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: 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;">7</td><td style="text-align: center;;">X</td><td style="text-align: center;;">QU3</td><td style="text-align: center;;"></td><td style="text-align: right;;"></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: 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;">8</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;">EX2</td><td style="text-align: right;;"></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: 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;">9</td><td style="text-align: center;;">X</td><td style="text-align: center;;"></td><td style="text-align: center;;">EX3</td><td style="text-align: right;;"></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: 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;">10</td><td style="text-align: center;;">Y</td><td style="text-align: center;;"></td><td style="text-align: center;;">EX1</td><td style="text-align: right;;"></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: 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;">11</td><td style="text-align: center;;">Z</td><td style="text-align: center;;">QU4</td><td style="text-align: center;;"></td><td style="text-align: right;;"></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: 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;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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><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;">13</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><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><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">Sheet4</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">F2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIFS(<font color="Green">Staff,IF(<font color="Purple">FREQUENCY(<font color="Teal">MATCH(<font color="#FF00FF">Staff,Staff,0</font>),MATCH(<font color="#FF00FF">Staff,Staff,0</font>)</font>)>0,Staff,""</font>),Qual,">"""</font>)>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIFS(<font color="Green">Staff,IF(<font color="Purple">FREQUENCY(<font color="Teal">MATCH(<font color="#FF00FF">Staff,Staff,0</font>),MATCH(<font color="#FF00FF">Staff,Staff,0</font>)</font>)>0,Staff,""</font>),Exam,">"""</font>)>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIFS(<font color="Green">Staff,IF(<font color="Purple">FREQUENCY(<font color="Teal">MATCH(<font color="#FF00FF">Staff,Staff,0</font>),MATCH(<font color="#FF00FF">Staff,Staff,0</font>)</font>)>0,Staff,""</font>),Qual,">""",Exam,">"""</font>)>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIFS(<font color="Green">Staff,IF(<font color="Purple">FREQUENCY(<font color="Teal">MATCH(<font color="#FF00FF">Staff,Staff,0</font>),MATCH(<font color="#FF00FF">Staff,Staff,0</font>)</font>)>0,Staff,""</font>),Qual,">""",Exam,""</font>)>0</font>)</font>)</td></tr></tbody></table></td></tr></table><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>Array 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">L2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",Staff,""</font>),IF(<font color="Teal">Qual<>"",Staff,""</font>),0</font>),MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",Staff,""</font>),IF(<font color="Teal">Qual<>"",Staff,""</font>),0</font>)</font>)>0</font>)</font>)-1}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">MATCH(<font color="Purple">IF(<font color="Teal">Exam<>"",Staff,""</font>),IF(<font color="Teal">Exam<>"",Staff,""</font>),0</font>),MATCH(<font color="Purple">IF(<font color="Teal">Exam<>"",Staff,""</font>),IF(<font color="Teal">Exam<>"",Staff,""</font>),0</font>)</font>)>0</font>)</font>)-1}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam<>"",Staff,""</font>),""</font>),IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam<>"",Staff,""</font>),""</font>),0</font>),MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam<>"",Staff,""</font>),""</font>),IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam<>"",Staff,""</font>),""</font>),0</font>)</font>)>0</font>)</font>)-1}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam="",Staff,""</font>),""</font>),IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam="",Staff,""</font>),""</font>),0</font>),MATCH(<font color="Purple">IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam="",Staff,""</font>),""</font>),IF(<font color="Teal">Qual<>"",IF(<font color="#FF00FF">Exam="",Staff,""</font>),""</font>),0</font>)</font>)>0</font>)</font>)-1}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><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 Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sheet4!Exam</th><td style="text-align:left">=OFFSET(<font color="Blue">Staff,0,COLUMNS(<font color="Red">Sheet4!$A$2:$C$2</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sheet4!Qual</th><td style="text-align:left">=OFFSET(<font color="Blue">Staff,0,COLUMNS(<font color="Red">Sheet4!$A$2:$B$2</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sheet4!Staff</th><td style="text-align:left">=Sheet4!$A$2:INDEX(<font color="Blue">Sheet4!$A:$A,MATCH(<font color="Red">REPT(<font color="Green">"z",9</font>),Sheet4!$A:$A,1</font>)</font>)</td></tr></tbody></table></td></tr></table>
Markmzz
 
Upvote 0
Hope you are on Excel 2007 or later and the interpretation I opted for is correct...

<TABLE style="WIDTH: 666pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=887><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1820" width=51><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3356" width=94><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2076" width=58><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" width=38><COL style="WIDTH: 403pt; mso-width-source: userset; mso-width-alt: 19114" width=538><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3840" width=108><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=51>Staff</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=94>Qualification</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=58>Exams</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=38></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 403pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=538></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=108>
Distinct Count​
</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>W</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=xl65></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=xl65>EX1</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=xl65></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=xl65>No. of staff that have qualifications</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=xl65 align=right>
3​
</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>W</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=xl65>QU1</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=xl65></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=xl65></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=xl65>No. of staff that currently studying for an exam</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=xl65 align=right>
3​
</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>W</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=xl65></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=xl65>EX2</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=xl65></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=xl65>No. of staff that have qualifications, that are also currently studying for an exam</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=xl65 align=right>
2​
</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</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=xl65>QU1</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=xl65></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=xl65></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=xl65>No. of staff that have qualifications, that not currently studying for an exam</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=xl65 align=right>
1​
</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</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=xl65>QU2</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=xl65></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=xl65></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=xl65></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=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</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=xl65>QU3</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=xl65></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=xl65></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=xl65></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=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</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=xl65></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=xl65>EX2</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=xl65></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=xl65></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=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>X</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=xl65></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=xl65>EX3</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=xl65></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=xl65></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=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Y</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=xl65></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=xl65>EX1</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=xl65></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=xl65></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=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Z</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=xl65>QU4</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=xl65></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=xl65></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=xl65></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=xl65></TD></TR></TBODY></TABLE>

F2, control+shift+enter, just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",IF($B$2:$B$11<>"",
MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1),1))

F3, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",IF($C$2:$C$11<>"",
 MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1),1))

F4, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(ISNUMBER(MATCH(IF(FREQUENCY(IF($A$2:$A$11<>"",
 IF($B$2:$B$11<>"",MATCH($A$2:$A$11,$A$2:$A$11,0))),
  ROW($A$2:$A$11)-ROW($A$2)+1),$A$2:$A$11),
   IF($C$2:$C$11<>"",$A$2:$A$11,"#"),0)),1))

F5, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(ISNUMBER(MATCH(IF(FREQUENCY(IF($A$2:$A$11<>"",
 IF($B$2:$B$11<>"",MATCH($A$2:$A$11,$A$2:$A$11,0))),
  ROW($A$2:$A$11)-ROW($A$2)+1),$A$2:$A$11),
   IF(ISNA(MATCH(A2:A11,IF($C$2:$C$11<>"",$A$2:$A$11,"#"),0)),
    $A$2:$A$11,"@"),0)),1))
 
Upvote 0
Thanks for all the advise everyone!

Unfortunately, I'm only actually on 2000 (!) as my office is woefully behind the times. Hopefully, the 2003 examples will work - I'll let you know when I'm back in the office.

Thanks again!
 
Upvote 0
I'm rubbish with Excel said:
Thanks for all the advise everyone!

Unfortunately, I'm only actually on 2000 (!) as my office is woefully behind the times. Hopefully, the 2003 examples will work - I'll let you know when I'm back in the office.

Thanks again!
Click to expand...

Look at this:

Excel 2000<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><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><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Staff name</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Qualification aattained</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Exams currently studying</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold"><-2003</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Qual</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Exam</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Qual & Exam</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Qual not Exam</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">W</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">EX1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">W</TD><TD style="TEXT-ALIGN: center">QU1</TD><TD style="TEXT-ALIGN: center"></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">4</TD><TD style="TEXT-ALIGN: center">W</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">EX2</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">5</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">QU1</TD><TD style="TEXT-ALIGN: center"></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">6</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">QU2</TD><TD style="TEXT-ALIGN: center"></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">7</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">QU3</TD><TD style="TEXT-ALIGN: center"></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">8</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">EX2</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">9</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">EX3</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">10</TD><TD style="TEXT-ALIGN: center">Y</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">EX1</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">11</TD><TD style="TEXT-ALIGN: center">Z</TD><TD style="TEXT-ALIGN: center">QU4</TD><TD style="TEXT-ALIGN: center"></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">12</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></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">13</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><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD></TR></TBODY></TABLE>Sheet4


With this formulas and names:

<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">Array 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>F2</TH><TD style="TEXT-ALIGN: left">{=SUM(--(FREQUENCY(MATCH(IF(Qual<>"",Staff,""),IF(Qual<>"",Staff,""),0),MATCH(IF(Qual<>"",Staff,""),IF(Qual<>"",Staff,""),0))>0))-1}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G2</TH><TD style="TEXT-ALIGN: left">{=SUM(--(FREQUENCY(MATCH(IF(Exam<>"",Staff,""),IF(Exam<>"",Staff,""),0),MATCH(IF(Exam<>"",Staff,""),IF(Exam<>"",Staff,""),0))>0))-1}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">{=SUM(--(FREQUENCY(MATCH(IF(Qual<>"",IF(Exam<>"",Staff,""),""),IF(Qual<>"",IF(Exam<>"",Staff,""),""),0),MATCH(IF(Qual<>"",IF(Exam<>"",Staff,""),""),IF(Qual<>"",IF(Exam<>"",Staff,""),""),0))>0))-1}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I2</TH><TD style="TEXT-ALIGN: left">{=SUM(--(FREQUENCY(MATCH(IF(Qual<>"",IF(Exam="",Staff,""),""),IF(Qual<>"",IF(Exam="",Staff,""),""),0),MATCH(IF(Qual<>"",IF(Exam="",Staff,""),""),IF(Qual<>"",IF(Exam="",Staff,""),""),0))>0))-1}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>
<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 Defined Names <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>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Exam</TH><TD style="TEXT-ALIGN: left">=OFFSET(Staff,0,COLUMNS(Sheet4!$A$2:$C$2)-1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Qual</TH><TD style="TEXT-ALIGN: left">=OFFSET(Staff,0,COLUMNS(Sheet4!$A$2:$B$2)-1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Staff</TH><TD style="TEXT-ALIGN: left">=Sheet4!$A$2:INDEX(Sheet4!$A:$A,MATCH(REPT("z",9),Sheet4!$A:$A,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
Here are all the names without the fucntion OFFSET:

Note: that work with Excel 2000 too.

Worksheet Defined Names
<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>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Exam</TH><TD style="TEXT-ALIGN: left">=Sheet4!$C$2:INDEX(Sheet4!$C:$C,MATCH(REPT("z",9),Sheet4!$A:$A,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Qual</TH><TD style="TEXT-ALIGN: left">=Sheet4!$B$2:INDEX(Sheet4!$B:$B,MATCH(REPT("z",9),Sheet4!$A:$A,1))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Sheet4!Staff</TH><TD style="TEXT-ALIGN: left">=Sheet4!$A$2:INDEX(Sheet4!$A:$A,MATCH(REPT("z",9),Sheet4!$A:$A,1))</TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
That's great, Markmzz, and it seems to be working ok.

However, can I just check something with you?

You have "Qual & Exam" (H2) as 0, but there are actually 2 staff members (W and X) that have both

Similarly, "Qual Not Exam" (I2) shows 3, but there is actually only 1 staff member (Y) studying for an exam without a qualification attained.

I hope that makes sense.

many thanks again
 
Upvote 0
I'm rubbish with Excel said:
That's great, Markmzz, and it seems to be working ok.

However, can I just check something with you?

You have "Qual & Exam" (H2) as 0, but there are actually 2 staff members (W and X) that have both

Similarly, "Qual Not Exam" (I2) shows 3, but there is actually only 1 staff member (Y) studying for an exam without a qualification attained.

I hope that makes sense.

many thanks again
Click to expand...

See my post and consequent question...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Unique count with a sumifs
2
Replies
10
Views
344
EmmaTM
E
T
  • Solved
Stop and start counting in different cells
Replies
9
Views
160
Turbos
T
FatalLordes
  • Question Question
Expand Cell depending on content
Replies
2
Views
414
FatalLordes
FatalLordes
A
  • Question Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
921
ashleighbrown
A
Burrgogi
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
924
Burrgogi
Burrgogi

Forum statistics

Threads
1,224,551
Messages
6,179,480
Members
452,915
Latest member
hannnahheileen

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