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. Currently I'm using the COUNTIF function, but it counts the hidden rows as well as the unhidden. I'm also aware of the SUBTOTAL function, but it doesn't have a COUNTIF part. I'd like to have something that I can put in a macro or whatever and have the users run it from here on out when programs are added.

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

Jody

[HOTPEPPER]

This assumes you have something set up to hide the rows you don't want already, but how about something like this?

Sub counting()
chkend = 0
x = 0
y = 0
z = 0
While chkend <> 1
x = x + 1
cvalue = Worksheets("Sheet1").Cells(x, 1)
cvalue2 = Worksheets("Sheet1").Cells(x, 3)
If cvalue = "" Then chkend = 1
If cvalue2 = "Proven" Then
y = y + Abs((1 * Worksheets("Sheet1").Rows(x).Hidden = False))
End If
If cvalue2 = "Unproven" Then
z = z + Abs((1 * Worksheets("Sheet1").Rows(x).Hidden = False))
End If
Wend
Worksheets("Sheet1").Cells(x + 1, 1) = "Proven:"
Worksheets("Sheet1").Cells(x + 1, 2) = y
Worksheets("Sheet1").Cells(x + 2, 1) = "Unproven:"
Worksheets("Sheet1").Cells(x + 2, 2) = z
Worksheets("Sheet1").Cells(x + 3, 1) = "Total:"
Worksheets("Sheet1").Cells(x + 3, 2) = y + z
End Sub

[Aladin Akyurek]

******** ******************** ************************************************************************>

Microsoft Excel - Book10 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
E2F2E3F3 =
	A	B	C	D	E	F
1	Machine#	Machine	Status	*	Proven	Unproven
2	12	Machine1	Proven	*	2	1
3	12	Machine2	Proven	*	2	1
4	14	Machine3	Unproven	*	*	*
5	14	Machine4	Proven	*	*	*
Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Two options...

E2, copied across, requires the morefunc.xll add-in...

=IF(COUNT($A$2:$A$5)*COUNTA($C$2:$C$5),COUNTDIFF(IF($C$2:$C$5=E1,$A$2:$A$5,FALSE))-1,"")

E3, copied across...

=SUM(IF(FREQUENCY(IF(($C$2:$C$5=E1)*($A$2:$A$5<>""),MATCH($A$2:$A$5,$A$2:$A$5,0),""),IF(($C$2:$C$5=E1)*($A$2:$A$5<>""),MATCH($A$2:$A$5,$A$2:$A$5,0),""))>0,1))

Each of these formulas must be committed with control+shift+enter instead of just with enter.