Filtering out duplicates and counting whats left in Excel.

[Jody72]

I've got an Excel file that lists programs by numerical file name, what machine they are for and wheter they are proven or unproven. At the bottom I have it set to count the # proven, # unproven, and then the total. I know how to filter out duplicates, but it just hides those rows and does not change my totals, which is what I need. I need to be able to hide any duplicates and have my count show only what is not hidden.

Here's an example:

ColA ColB ColC
12 Machine1 Proven
12 Machine2 Proven
14 Machine3 Unproven
15 Machine4 Proven

Total Proven: 3
Total Unproven: 1
total: 4

Results being:

ColA ColB ColC
12 Machine1 Proven
14 Machine2 Proven
14 Machine4 unproven

Proven: 2
Unproven: 1
Total: 3

The machine numbers are irrelevant, I just added those to show why there would be duplicates of filenames.

Thanks for any and all help in advance. My knowledge of something like this is virtually nil.

Jody

 

[PHV]

Take a look at the SUBTOTAL worksheet function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Hi,

You can use the COUNTIF function
[tt]
=COUNTIF($C:$C,E1)
[/tt]
where E1 contains Proven or unproven

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Jody72]

Skip,

Yeah, Ive got that much, that's how I get the results I'm getting now. But it also counts the hidden rows after I filter. I need a count post-filter.

 

[Jody72]

PH,

Thanks for the SUBTOTAL suggestion, I wasn't aware of it. Only problem is that there is no COUNTIF function.

 

[SkipVought]

Use the Advanced Filter to Copy Unique Values (no Criteria) to a New Location. Then COUNTIF on the Filtered Data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Jody72]

Thanks Skip, didn't consider that.