Formula for unique record count - do not count hidden records using autofilter

[deskjocky]

Hello,

I have gotten pretty close to what I need, but I have been struggling with a formula that counts OVERDUE records. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The unique records being counted are in column C (for both formulas).<o></o>
<o></o>
The formula in E2 gives me the correct count of unique records. When I use the auto filter, the count is changed according to the displayed records in column C.<o></o>
<o></o>
The formula in E3 counts the unique records matching OVERDUE. The Count stays the same when, using the auto filter, and OVERDUE is not displayed.<o></o>
<o></o>
Is there a way to have my unique record count, based on OVERDUE, only count when OVERDUE is displayed?<o></o>

Overdue & Critical


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 166px"><COL style="WIDTH: 93px"><COL style="WIDTH: 405px"><COL style="WIDTH: 65px"><COL style="WIDTH: 53px"><COL style="WIDTH: 77px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR><TR style="HEIGHT: 24px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">Data as of: </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; TEXT-ALIGN: right">2/25/2010</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">Unique Task Count for this report = </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">26</TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR><TR style="HEIGHT: 24px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">Unique Overdue Task Count for this report =</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: #ffffff; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: center">3</TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR><TR style="HEIGHT: 27px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 16pt; COLOR: #c0c4c7"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Facility</TD><TD>Task (Number)</TD><TD>Task Name</TD><TD>Due Date</TD><TD>Priority</TD><TD style="BACKGROUND-COLOR: #f7f3ef">Task Status</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Ashton</TD><TD style="TEXT-ALIGN: right">1063298</TD><TD>text 2</TD><TD style="TEXT-ALIGN: right">3/5/2008</TD><TD>Medium</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>JC Boyle</TD><TD style="TEXT-ALIGN: right">1054634</TD><TD>text 24</TD><TD style="TEXT-ALIGN: right">12/31/2009</TD><TD>High</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>JC Boyle</TD><TD style="TEXT-ALIGN: right">1054634</TD><TD>text 25</TD><TD style="TEXT-ALIGN: right">12/31/2009</TD><TD>High</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>JC Boyle</TD><TD style="TEXT-ALIGN: right">1054634</TD><TD>text 26</TD><TD style="TEXT-ALIGN: right">12/31/2009</TD><TD>High</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>JC Boyle</TD><TD style="TEXT-ALIGN: right">1054634</TD><TD>text 27</TD><TD style="TEXT-ALIGN: right">12/31/2009</TD><TD>High</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Merwin</TD><TD style="TEXT-ALIGN: right">1046437</TD><TD>text 28</TD><TD style="TEXT-ALIGN: right">2/5/2010</TD><TD>Medium</TD><TD style="COLOR: #ffffff; BACKGROUND-COLOR: #ff3031">OVERDUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Cutler</TD><TD style="TEXT-ALIGN: right">1045940</TD><TD>text 1</TD><TD style="TEXT-ALIGN: right">3/30/2010</TD><TD>Low</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Ashton</TD><TD style="TEXT-ALIGN: right">1063299</TD><TD>text 3</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>Bear River Project</TD><TD style="TEXT-ALIGN: right">1044483</TD><TD>text 4</TD><TD style="TEXT-ALIGN: right">3/1/2010</TD><TD>High</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Grace</TD><TD style="TEXT-ALIGN: right">1052073</TD><TD>text 5</TD><TD style="TEXT-ALIGN: right">5/5/2010</TD><TD>High</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Grace</TD><TD style="TEXT-ALIGN: right">1063332</TD><TD>text 6</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Grace</TD><TD style="TEXT-ALIGN: right">1063333</TD><TD>text 7</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Grace</TD><TD style="TEXT-ALIGN: right">1063334</TD><TD>text 8</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Last Chance</TD><TD style="TEXT-ALIGN: right">1063330</TD><TD>text 9</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>Last Chance</TD><TD style="TEXT-ALIGN: right">1063331</TD><TD>text 10</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>Lifton</TD><TD style="TEXT-ALIGN: right">1063308</TD><TD>text 11</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Lifton</TD><TD style="TEXT-ALIGN: right">1063314</TD><TD>text 12</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>Lifton</TD><TD style="TEXT-ALIGN: right">1063315</TD><TD>text 13</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>Oneida</TD><TD style="TEXT-ALIGN: right">1063316</TD><TD>text 14</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>Oneida</TD><TD style="TEXT-ALIGN: right">1063317</TD><TD>text 15</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD>Oneida</TD><TD style="TEXT-ALIGN: right">1063318</TD><TD>text 16</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD>Oneida</TD><TD style="TEXT-ALIGN: right">1063319</TD><TD>text 17</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD>Paris</TD><TD style="TEXT-ALIGN: right">1063320</TD><TD>text 18</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD>Paris</TD><TD style="TEXT-ALIGN: right">1063321</TD><TD>text 19</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>Soda Idaho</TD><TD style="TEXT-ALIGN: right">1063328</TD><TD>text 20</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD>Soda Idaho</TD><TD style="TEXT-ALIGN: right">1063329</TD><TD>text 21</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD>St. Anthony</TD><TD style="TEXT-ALIGN: right">1063322</TD><TD>text 22</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD>St. Anthony</TD><TD style="TEXT-ALIGN: right">1063326</TD><TD>text 23</TD><TD style="TEXT-ALIGN: right">3/5/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD>Prospect 1</TD><TD style="TEXT-ALIGN: right">1060133</TD><TD>text 29</TD><TD style="TEXT-ALIGN: right">3/4/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD>Prospect 2</TD><TD style="TEXT-ALIGN: right">1060133</TD><TD>text 30</TD><TD style="TEXT-ALIGN: right">3/4/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD>Prospect 4</TD><TD style="TEXT-ALIGN: right">1060133</TD><TD>text 31</TD><TD style="TEXT-ALIGN: right">3/4/2010</TD><TD>Medium</TD><TD style="BACKGROUND-COLOR: #ffff00">CRITICAL</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #efebde"></TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=Sheet1!B38</TD></TR><TR><TD>E2</TD><TD>{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C7:C700,ROW(C7:C700)-ROW(C7),0,1)),MATCH("~"&C7:C700,C7:C700&"",0)),ROW(C7:C700)-ROW(C7)+1),1))}</TD></TR><TR><TD>E3</TD><TD>{=SUM(IF(FREQUENCY(IF(G7:G100="OVERDUE",IF(C7:C100<>"",MATCH(C7:C100,C7:C100,0))),ROW(C7:C100)-ROW(C7)+1),1))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Thanks, in advance, for any help, hints, tips, pointers, etc.
Lloyd

 

[Weaver]

I don't know if this will help but have you looked at the SUBTOTAL() formula. There's an argument you can specify which allows counts or totals of only the visible rows in a filter set. Not sure if this will fit into your complex formulas or replace them

HTH

 

[deskjocky]

I don't know if this will help but have you looked at the SUBTOTAL() formula. There's an argument you can specify which allows counts or totals of only the visible rows in a filter set. Not sure if this will fit into your complex formulas or replace them

HTH

Hi,

I tried a few variations, trying to use the SUBTOTAL() formula setting. I either got a "too few arguments used" or the "formula error" type messages.

I am not an expert with these formulas, but I am trying to understand how they work. As a matter of fact, I got these formulas from reading other posts in this great forum.