Please Help - Complex "Count" Formula Needed on Filtered Cells

diecast61

New Member
Joined
May 18, 2009
Messages
6
I'm hoping that someone can help me.

I need a formula that can provide counts with the following criteria - please assume XL2003. I have searched and searched - to no avail.

1. Data is filtered on one or more columns Assume data is filtered on column D - to exclude "Free"
2. Data Range (unfiltered) A8:A1000
2. I want to return a count of Unique values in column A - only visible cells.
3. However I haveadditional criteria (subject to change) in the data range - for example - where B8:B1000 <> "ABC" and F8:F:1000=57

This is basically the equivalent of a Select Distinct in SQL with multiple criteria returning a count.

I basically need to be able to combine Sumproduct (multiple criteria) and Subtotal (since the data is filtered).

In short I need to be able to show and update multiple counts on filtered data while specifying additional criteria in the formula such that the counts change as the filter changes.

Thanks in advance - if additional information is needed to understand what I am looking for - please let me know.
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello diecast61, welcome to MrExcel, this formula will return a count of unique visible values in column A

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8,ROW(A8:A1000)-ROW(A8),0)),MATCH(A8:A1000,A8:A1000,0)),ROW(A8:A1000)-ROW(A8)+1),1))

Needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

To get a unique count with the additional criteria you listed try this version

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8,ROW(A8:A1000)-ROW(A8),0)),IF(B8:B1000<>"ABC",IF(F8:F1000=57,MATCH(A8:A1000,A8:A1000,0)))),ROW(A8:A1000)-ROW(A8)+1),1))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8:A1000,ROW(A8:A1000)-ROW(A8),0,1)),IF(B8:B1000<>"ABC",IF(F8:F1000=57,MATCH("~"&A8:A1000,A8:A1000&"",0)))),ROW(A8:A1000)-ROW(A8)+1),1))

Note, however, that COUNTDIFF is more efficient. The function is included in the free add-in, Morefunc.xll.
 

diecast61

New Member
Joined
May 18, 2009
Messages
6
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8:A1000,ROW(A8:A1000)-ROW(A8),0,1)),IF(B8:B1000<>"ABC",IF(F8:F1000=57,MATCH("~"&A8:A1000,A8:A1000&"",0)))),ROW(A8:A1000)-ROW(A8)+1),1))

Note, however, that COUNTDIFF is more efficient. The function is included in the free add-in, Morefunc.xll.
I would love to use the add-in - however I am using 2007 - and have not figured out how to embed the functions - as others will need to use this file as well. Others will be using 2003 - hence the need for compatibility with 2003. Do you know if they have resolved this issue yet?

Thanks
 

diecast61

New Member
Joined
May 18, 2009
Messages
6
Hello diecast61, welcome to MrExcel, this formula will return a count of unique visible values in column A

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8,ROW(A8:A1000)-ROW(A8),0)),MATCH(A8:A1000,A8:A1000,0)),ROW(A8:A1000)-ROW(A8)+1),1))

Needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

To get a unique count with the additional criteria you listed try this version

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A8,ROW(A8:A1000)-ROW(A8),0)),IF(B8:B1000<>"ABC",IF(F8:F1000=57,MATCH(A8:A1000,A8:A1000,0)))),ROW(A8:A1000)-ROW(A8)+1),1))
Thanks - will try this and report back -- have my fingers crossed.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
I would love to use the add-in - however I am using 2007 - and have not figured out how to embed the functions - as others will need to use this file as well. Others will be using 2003 - hence the need for compatibility with 2003. Do you know if they have resolved this issue yet?

Thanks
As far as I know, the add-in is compatible with Excel 95 to 2007. Once the add-in is installed, there's an option which easily allows one to embed it within the file itself. So there's no need for others to install the add-in on their machines. The one drawback, though, is that it's not compatible with Mac computers.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,193
Hi.

You might like to use the SQL "SELECT DISTINCT ..." in a query table or ADO recordset?

Query tables can be done without VBA: with VBA will be better. How it is best done for your use depends on your requirements - which are not 100% clear to me. Such as, if you want one or two specific counts, or the flexibility to do many.

There will be many old posts with examples of query tables or you can google for examples. In Excel, they are under menu data, import external data, new database query.

You might use a pivot table too, if you are careful to handle the 'DSITINCT' aspect.

Regards, Fazza
 
Last edited:

Forum statistics

Threads
1,082,480
Messages
5,365,798
Members
400,854
Latest member
zenith_11

Some videos you may like

This Week's Hot Topics

Top