Subtotal of the Frequency with Multiple Criteria

[cornetazo]

Hi,

I need to calculate the subtotal of a frquency of unique values with multiple criteria.

Example:
<TABLE style="WIDTH: 228pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=303 border=0 x:str><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 24pt" height=32><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 82pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 24pt; BACKGROUND-COLOR: #ffcc99" width=109 height=32>Person</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc" width=130>Current Status</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc" width=64>CRA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Zissman, Edward</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Roetzheim, Richard</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Roetzheim, Richard</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Lowenstein, Mitchell</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Landis, Miles</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hale, Gregory</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Start-Up</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Godofsky, Eliot</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Recruitment</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Giuliano, Anna</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Maintenance</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Venigalla, Madhavi</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Start-Up</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Alrabaa, Sally</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Valid./Recommend</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ali, Mohamed</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Valid./Recommend</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Cohen, Lisa</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Valid./Recommend</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Godofsky, Eliot</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Site Closed</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Crespo, Isreal</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Valid./Recommend</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 82pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=109 height=34>Crespo, Isreal</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Valid./Recommend</TD><TD class=xl30 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">Doe, John</TD></TR></TBODY></TABLE>

What I need is to know how many unique people are associated with John Doe that have a current status of "maintenance" and/or "start-up".

The formula I currently have is:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E11:E500,ROW(E11:E500)-ROW(E11),0,1)),MATCH("~"&E11:E500,E11:E500&"",0)),ROW(E11:E500)-ROW(E11)+1)>0,1))

but it does not account for the current status. The result I get from my formula is 12 but the result I want is 8 since there are only 8 unique people with a status of "maintenance" and/or "start-up" that are associated with John Doe.

I know I can Pivot this info but was hoping to create a formula for this information.

Thanks.......

 

[barry houdini]

Hello cornetazo, welcome to MrExcel. Try changing to this version

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E11:E500,ROW(E11:E500)-ROW(E11),0,1)),IF(ISNUMBER(MATCH(F11:F500,{"Maintenance","Start-Up"},0)),MATCH("~"&E11:E500,E11:E500&"",0))),ROW(E11:E500)-ROW(E11)+1)>0,1))

 

[markmzz]

Maybe Advanced Filter. Look at this:

<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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Person</td><td style="font-weight: bold;;">Current Status</td><td style="font-weight: bold;;">CRA</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">CRA</td><td style="font-weight: bold;;">Current Status</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Person</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Zissman, Edward</td><td style=";">Maintenance</td><td style=";">Doe, John</td><td style="text-align: right;;"></td><td style=";">Doe, John</td><td style=";">Maintenance</td><td style="text-align: right;;"></td><td style=";">Zissman, Edward</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Roetzheim, Richard</td><td style=";">Maintenance</td><td style=";">Doe, John</td><td style="text-align: right;;"></td><td style=";">Doe, John</td><td style=";">Start-Up</td><td style="text-align: right;;"></td><td style=";">Roetzheim, Richard</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Roetzheim, Richard</td><td style=";">Maintenance</td><td style=";">Doe, John</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=";">Lowenstein, Mitchell</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Lowenstein, Mitchell</td><td style=";">Maintenance</td><td style=";">Doe, John</td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Criteria</td><td style="background-color: #FFFF00;;">Area</td><td style="text-align: right;;"></td><td style=";">Landis, Miles</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Landis, Miles</td><td style=";">Maintenance</td><td style=";">Doe, John</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=";">Hale, Gregory</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Hale, Gregory</td><td style=";">Start-Up</td><td style=";">Doe, John</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=";">Giuliano, Anna</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Godofsky, Eliot</td><td style=";">Recruitment</td><td style=";">Doe, John</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=";">Venigalla, Madhavi</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Giuliano, Anna</td><td style=";">Maintenance</td><td style=";">Doe, John</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=";">Venigalla, Madhavi</td><td style=";">Start-Up</td><td style=";">Doe, John</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="background-color: #FFFF00;;">Out Area</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Alrabaa, Sally</td><td style=";">Valid./Recommend</td><td style=";">Doe, John</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=";">Ali, Mohamed</td><td style=";">Valid./Recommend</td><td style=";">Doe, John</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=";">Cohen, Lisa</td><td style=";">Valid./Recommend</td><td style=";">Doe, John</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;">14</td><td style=";">Godofsky, Eliot</td><td style=";">Site Closed</td><td style=";">Doe, John</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;">15</td><td style=";">Crespo, Isreal</td><td style=";">Valid./Recommend</td><td style=";">Doe, John</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;">16</td><td style=";">Crespo, Isreal</td><td style=";">Valid./Recommend</td><td style=";">Doe, John</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;">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><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="background-color: #FFFF00;;">In Area</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Advanced</td><td style="background-color: #FFFF00;;">Filter</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;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF00;;">Only</td><td style="background-color: #FFFF00;;">Unique</td><td style="background-color: #FFFF00;;">Records</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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">Sheet5</p><br /><br />
Markmzz

 

[cornetazo]

Worked perfectly!!!

Thanks a million.