Filtering based on criteria

[50gumbys]

I'm not sure what I need here, but if you have any suggestions - I'd very much appreciate them!

I'm using a list and filtering out records (using Auto Filter).
So my list starts out looking something like this:

Date Month Type Doc #
18-May May PTW 1290683
18-May May PTW 1290686
18-May May LB 1
18-May May PTW 1290686
18-May May LB 9
18-May May PTW 1283063
18-May May PTW 1290683
20-May May PTW 1293386

When I filter out the 'Type' column to see only 'LB' documents;
I'll see only the entries for 'LB'. (in the above example,
the number of 'LB' documents would be 2).

Above this list I would like the number of LB documents (2) displayed - something like this.
Number of LB Documents: 2

No problem so far - I use the SUBTOTAL function.

However!

There is another option in this column - 'PTW'. And I'd like THAT to be displayed as well, if the list is filtered out for 'PTW' entries.

So my display would look something like:

Number of LB Documents: 2
Number of PTW Documents:

What I want is to display an answer if LB is selected (and thus display nothing for PTW documents). But, if PTW is selected I want that to display, but nothing for LB documents. like this -

Number of LB Documents:
Number of PTW Documents: 6

So I tried this calculation:

=IF(OFFSET(C27,1,0)="PTW",SUBTOTAL(3,PTWList)," ")
&IF(OFFSET(C27,1,0)="LB",SUBTOTAL(3,PWTList)," ")

I used OFFSET to determine the entry in cell C28 (when filtered).
If the entry is PTW, do the subtotal. PTWList is the named range of cells under the column heading in cell C27.

vice versa if it the entry is LB.

Unfortunately, it ain't working!

It just adds up, no matter whether PTW or LB is selected.

Am I simply using the wrong function; or is this a job for ...
VB Man!


Can anyone help me?
Thank you SO much!!


__________________
Lori Hughes

[Richie(UK)]

Hi Lori,

OK, I entered your example data at the start of a new sheet and applied the autofilter.

In C11 I entered the formula for the LB count as:

=IF(LEFT(C13,2)="LB",SUBTOTAL(3,C2:C9),0) 

In C12 I entered the formula for the PTW count as:

=IF(LEFT(C13,3)="PTW",SUBTOTAL(3,C2:C9),0) 

And finally, in C13 I array-entered (Ctrl + Shift + Enter) the following:

=INDEX(C1:C10,MIN(IF(SUBTOTAL(3,OFFSET(C2:C10,ROW(C2:C10)-MIN(ROW(C2:C10)),,1))<>0,SUBTOTAL(3,OFFSET(C2:C10,ROW(C2:C10)-MIN(ROW(C2:C10)),,1))*ROW(C2:C10)))) 

The array formula determines the value of the first row (excluding the header) in column C. This is then used by the two Subtotal functions.

All credit to PaddyD at MrExcel for the formula.

HTH

[CBrine]

Not to complicate the matter, but how about this. Place all of your subttotals within the filtered data.

Column B ="Number of"
Column C = "PTW"
Column D = " Documents: " & Countif(C2:C????,"PTW")

Do the countif for each type of criteria(LB,PTW,etc...)

Now when you apply the filter to column C, it will show all PTW's including the countif showing how many exist. No code involved.(Sorry Dreamboat)

[50gumbys]

Thank you so much for your input - I really appreciate that.
Richie - that really is major! And it makes me think it's related to my little effort.

But for ease of use, CBrine's offer looks very good.

Now, my problem is how do I actually make use of your solution?
I find that the 'Countif' function counts the contents of the cell, whether displayed or not (which is why I used the SubTotal function).

Sorry - I know this place was strictly for VBA.

[CBrine]

50gumbys,

I must have read your post wrong, I was under the impression that you wanted the counts to appear regardless of the filtering. It seems that you want the total line to appear, but with no value. My solution will show the total counts no matter what. I don't think you want this. I would suggest you try Richie's solution.

[tommy bak]

a small userdefined function could also help
insert this i a standard module

Function CountVisi(Data As Range, Ref As Variant) As Variant
Dim C As Range, lcnt As Long
lcnt = 0
For Each C In Data
    If (Not C.Rows.Hidden) Then
        If C Like Ref & "*" Then lcnt = lcnt + 1
    End If
Next
If lcnt = 0 Then CountVisi = "" Else CountVisi = lcnt
End Function

Can be used on the worksheet like this

=CountVisi($C$5:$C$100;"LB")
=CountVisi($C$5:$C$100;"PTW")

or by refering to a cell, if it says LB in B1 and PTW in B2

=CountVisi($C$5:$C$100;B1)
=CountVisi($C$5:$C$100;B2)

BR
Tommy Bak

[50gumbys]

Hello!
Please let me apologise for not being here lately to respond to your help.
I won't even go into what's been happening here, suffice to say - thank you guys for all your help. I have really appreciated the thought you've put into this and of course, your input.

Tommy, I've tried your option.
Mate - You are wonderful !!!!!!!!!!!!!!!!!!!!!!!

I know I've said this before - but it's true - it works just like a *store bought* one!

Thank you! Thank you! Thank you!!

This is going to solve SO many problems!!!

Oh, did I say Thank You !! ????

[lucas]

Lori and Tommy,
I get an error when I try to enter the formulas in the cells to run Tommy's udf from this thread. Any idea why. I tried entering them several different ways and excel still rejects them. I have the function in a module.

I was able to get Mr. Richie's method with the array to work though.

just wondering what I could be doing wrong.

[Zack Barresse]

Steve,

Are you using it on a new file? Bookx..?

[lucas]

Zack,
I have tried it on a new book and a saved book. Still no luck. Did it work for you?

[Zack Barresse]

Yes, it worked for me. I just needed to take out the semicolon in his formulas and replace it as I've got the English version and he doesn't (Swedish?).

[tommy bak]

I think you're right, Zack. I keep forgetting that most people here use english/US languagesettings where comma is the sepeartor and not semicolon.
btw. I use a Danish version, so you were close :-)

br
Tommy Bak

[lucas]

That did the trick. I had tried to replace the semi-colon with a colon and that had not worked either. I didn't think to try a comma.

[Zack Barresse]

Glad it worked Steve! And you know how confusing it is to call you Steve when I want to call you lucas! They're both first names! LOL Btw, how's the book? You got it yet? I'd love to hear about it.

Tommy: I won't forget again!

[lucas]

Zack,

I answer to either name. As you said they are both good for first names. Grew up being called Luke. As to the book, I haven't gotten it yet. The one I won is on Word so I will probably get a lot from it. I've been using Word for years but there are bound to be things I don't know about it.

In the mean time I have ordered VBA Macros for MS Excel from MrExcel staffers Bill Jelen and Tracy Syrstad as this is the software I am most involved in. I am just learning the VBA aspect of Excel and MS although I have used Excel for years. I also obtained a book from the local library called "Writing Excel Macros" by Steven Roman which is walking me through many of the most basic concepts, like defining variables which I didn't understand at all until you guys convinced me to use option explicit. Then I had to figure it out.(kinda) I still use variant to define a lot, although I understand now that there are drawbacks to using a catch-all like that just because you don't completly understand.

I also ordered a book from amazon which I got used for $5.75 used. which was recommended by Tommy and rvetrano called Mastering Autocad VBA which is another program I use a lot. So I am on a learning curve here. Sure glad I found this place. I learn a lot just reading the posts.

Thanks again Zack,

[Zack Barresse]

In the mean time I have ordered VBA Macros for MS Excel from MrExcel staffers Bill Jelen and Tracy Syrstad

I just picked that book up at Barnes & Nobles this last weekend (only to read, didn't buy) and it looked very informative. Good reviews thus far.

as this is the software I am most involved in. I am just learning the VBA aspect of Excel and MS although I have used Excel for years.

Me too. I think I only used SUM, MIN, MAX & AVERAGE before this last December, so I'm on a learning spree.

I learn a lot just reading the posts.

Me too! This is like a pool of brilliance!

Take care Steve!

[50gumbys]

Sorry guys - I should have posted about the semi-colon myself.
I couldn't get it to work myself, then had a closer look at it and saw the semi-colon. By that time I couldn't figure out if I had done it (altho I don't know why!), or not!!

And this is called *middle age* !!

Glad you got it working Luke!