Subtotal Table Values but exclude subsequent Rows in which text from another column is duplicated

[Joe813210]

A table of data that holds records of multiple projects. Projects may be duplicated within the table however, when they are, their Total Units are always the same number.

Obviously, a problem with this is that I cannot easily provide a sum or subtotal of the Total Units without double counting any duplicated project's Total Units within the Total Row. I am able to count only uniquely named projects, and outside the table, sum the total number of unique units. However I'm stuck at how I might be able to to filter by any other column within the table and have a Subtotaled sum of just the filtered (only visible) Total Units which still effectively provides a Sum which doesn't include duplicate Projects.

Data is in "Table1"
[Project] column is text
[Total Units] are numbers

I've used the following to remove duplicate named projects within the table's Total Row:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D4,ROW([Project])-ROW(D4),,1)),IF([Project]<>"",MATCH("~"&[Project],[Project]&"",0))),ROW([Project])-ROW(D4)+1),1))}

and the following to effectively provide a sum of Total Units without any duplicate projects for all records in the table, in a cell outside of the table:

{=SUMPRODUCT(Table1[Total Units],IF(Table1[Project]="",0,1/COUNTIF(Table1[Project],Table1[Project])))}

Is there a way to combine these processes to provide a count when the data is filtered?
Or is the solution I seek down a different thought process?
I'm open to any solutions, thoughts, or suggestions.

 

[Peter_SSs]

Not sure I am understanding your description and we have no sample data or layout to go on, but if you are just trying to get the total units for a project, does this help?

Excel Workbook
	A	B	C	D	E
1	Project	Units		Project 1
2	Project 1	2		Total Units	10
3	Project 1	2
4	Project 1	3
5	Project 1	2
6	Project 1	1
7	Project 2	6
8	Project 2	5
9	Project 2	4
10	Project 1	6
11	Project 1	1
12	Project 1	1
13	Project 1	2
Project Total

 

[Joe813210]

Not sure I am understanding your description and we have no sample data or layout to go on, but if you are just trying to get the total units for a project, does this help?

Unfortunately, I am not wanting to have to define the criteria to be summed such as is done in cell D1 of your example as there are multiple other criteria which could be used to filter this table. Rather I am hoping to be provided a sum, at the bottom of my table or in a dedicated cell based on the currently visible (filtered) table's data.

Apologies for not providing an example in the original post to help better explain. Below is a filtered table example to help explain the issue. This example is filtered by "Region" and then to exclude all blank "Adtl Program(s)"

Row 96 is the "Total Row" of "Table1"

Formulas:
B96 =SUBTOTAL(103,[ID '#])
D96 {=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E3,ROW([Project])-ROW(E3),,1)),IF([Project]<>"",MATCH("~"&[Project],[Project]&"",0))),ROW([Project])-ROW(E3)+1),1))}
G96 =SUBTOTAL(109,[Total Units])
H96 =SUBTOTAL(109,[Restricted Units])
G98 {=SUMPRODUCT(Table1[Total Units],IF(Table1[Project]="",0,1/COUNTIF(Table1[Project],Table1[Project])))}
H98 {=SUMPRODUCT(Table1[Restricted Units],IF(Table1[Project]="",0,1/COUNTIF(Table1[Project],Table1[Project])))}


[TABLE="width: 792"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID #
[/TD]
[TD]Program
[/TD]
[TD]Adtl Program(s)
[/TD]
[TD]Project
[/TD]
[TD]City
[/TD]
[TD]Region
[/TD]
[TD]Total Units
[/TD]
[TD]Restricted Units
[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]ID37
[/TD]
[TD]Prog 1
[/TD]
[TD]Prog 5, Prog 6, Prog 9
[/TD]
[TD]Project 33
[/TD]
[TD]City29
[/TD]
[TD]1
[/TD]
[TD]44
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD]ID38
[/TD]
[TD]Prog 5
[/TD]
[TD]Prog 1, Prog 6, Prog 9
[/TD]
[TD]Project 33
[/TD]
[TD]City29
[/TD]
[TD]1
[/TD]
[TD]44
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]ID39
[/TD]
[TD]Prog 6
[/TD]
[TD]Prog 1, Prog 5, Prog 9
[/TD]
[TD]Project 33
[/TD]
[TD]City29
[/TD]
[TD]1
[/TD]
[TD]44
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]54
[/TD]
[TD]ID53
[/TD]
[TD]Prog 6
[/TD]
[TD]Prog 9
[/TD]
[TD]Project 47
[/TD]
[TD]City27
[/TD]
[TD]1
[/TD]
[TD]26
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]82
[/TD]
[TD]ID82
[/TD]
[TD]Prog 3
[/TD]
[TD]Prog 6
[/TD]
[TD]Project 71
[/TD]
[TD]City6
[/TD]
[TD]1
[/TD]
[TD]26
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]83
[/TD]
[TD]ID81
[/TD]
[TD]Prog 6
[/TD]
[TD]Prog 3
[/TD]
[TD]Project 71
[/TD]
[TD]City6
[/TD]
[TD]1
[/TD]
[TD]26
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]90
[/TD]
[TD]ID89
[/TD]
[TD]Prog 5
[/TD]
[TD]Prog 9
[/TD]
[TD]Project 78
[/TD]
[TD]City29
[/TD]
[TD]1
[/TD]
[TD]51
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]91
[/TD]
[TD]ID90
[/TD]
[TD]Prog 2
[/TD]
[TD]Prog 4
[/TD]
[TD]Project 79
[/TD]
[TD]City28
[/TD]
[TD]1
[/TD]
[TD]42
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]92
[/TD]
[TD]ID91
[/TD]
[TD]Prog 4
[/TD]
[TD]Prog 2
[/TD]
[TD]Project 79
[/TD]
[TD]City28
[/TD]
[TD]1
[/TD]
[TD]42
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]96
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]345
[/TD]
[TD]246
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Unique Units[/TD]
[TD]2220[/TD]
[TD]1615[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So, what I would like done is to either have G96 & H96 already remove the duplicate's from that Subtotals OR have a cell outside "Table1" which sums the currently filtered (visible) data in "Table1" to provide a Subtotal of only those Unique Units. Essentially, making the formulas from G98 and H98 look at ONLY the filtered (visible) table's data, rather than always returning values based on the entire table's data even when filtered.

 

[Peter_SSs]

I'm struggling to understand, sorry.
For that sample (filtered) data,
- what result(s) do you expect?
- how did you get those results (manually)?

 

[Joe813210]

As for the results; No, not manually, I provided the formula's I used to derive those results. I doubt you wanted me to share all ~100 records.

In this scenario: I want a cell that would return 189 for "Total Units" because there are a total of 156 units being double counted in the 345 result (units from the same "Project Name").

In A96 I get a result of 9 because there are nine records in this filtered table, this is correct & works how I need it to. However, in D96 where it only returns a result of 5 because there are 5 uniquely named projects in this filtered table, this is also correct & works how I need it to.

My issue is that in G96 I get a subtotal for this column which includes ALL records even though some of the project names are duplicates (9 records but only 5 uniquely named projects)(and yes, I realize that the formula I currently have will not provide me this desired outcome and is working correctly).

My hope is that I can get a formula developed to provide me a subtotal for the "Total Units" column based on the table's current filter(s). As of now, all I can manage to do is provide unique subtotals for the entire Table (G98). When I filter that table, the total in this cell does not change because its not smart enough to know that I have filtered the table's data and want it to look only at that filtered data.

 

[Peter_SSs]

As for the results; No, not manually, ..

Language problem - probably my fault. The results I was asking about were the 'expected' ones I had asked for in the previous line.
Anyway, you gave me the answer to that none-the-less, thanks.

The SUBTOTAL function is not my strongest point but if I have understood correctly, here is one way to get your result.
I have added an extra column [Marker] to the table. That column can be hidden if you need once the formula has been entered.
Then, a fairly simple formula in G96 and H96 to get your results. Hope you can work with that.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID #	Program	Adtl Program(s)	Project	City	Region	Total Units	Restricted Units	Marker
2	xxxx			9999999			0	0	1
38	ID37	Prog 1	Prog 5, Prog 6, Prog 9	Project 33	City29	1	44	44	1
39	ID38	Prog 5	Prog 1, Prog 6, Prog 9	Project 33	City29	1	44	44	2
40	ID39	Prog 6	Prog 1, Prog 5, Prog 9	Project 33	City29	1	44	44	3
54	ID53	Prog 6	Prog 9	Project 47	City27	1	26	12	1
82	ID82	Prog 3	Prog 6	Project 71	City6	1	26	14	1
83	ID81	Prog 6	Prog 3	Project 71	City6	1	26	2	2
90	ID89	Prog 5	Prog 9	Project 78	City29	1	51	50	1
91	ID90	Prog 2	Prog 4	Project 79	City28	1	42	18	1
92	ID91	Prog 4	Prog 2	Project 79	City28	1	42	18	2
96							189	138
Sheet1

 

[Joe813210]

Excel-lent solution Peter_SSs!! Thank you.

 

[Peter_SSs]

Excel-lent solution Peter_SSs!! Thank you.

You are welcome.

BTW, I mentioned that SUBTOTAL was not my strongest point and I had 'stolen' that I2 formula from elsewhere.
On revisiting that formula I think that it can be simplified slightly by removing one function.
=SUMPRODUCT((D$2:D2=D2)*(SUBTOTAL(103,OFFSET(D$2,ROW(D$2:D2)-ROW(D$2),0))))