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

[diecast61]

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.

 

[barry houdini]

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]

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]

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]

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]

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]

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