counting unique value only in a filtering table help

Hi,

I need to count number of employee on each project we have. problem is some employee will work on different job in the same day.

I want to be able to filter: job 1 and return lets say 12 emp. then filter job 2 only and return 10 emp. but if I select all job as my filter it would return lets say 15 emp. as some of them worked on both job that day.

I think I would need a helper column to show 1 if unique and 0 for the duplicated value. Problem is every formula I tried will not work with the filtering option.

lets say I filter only job #2 then every emp. should have a 1 but some will have a 0 because they were also in job #1.


also if I can get this formula working would it also consider it in a pivot table?

see attached example

In L8

=COUNTIF($B$3:$B$21,K8)

In O8:

=COUNTIFS($A$3:$A$21,$O$5,$B$3:$B$21,N8)

Fill down.

Try this array formula**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A3,ROW(A3:A21)-ROW(A3),0,1)),MATCH(C3:C21,C3:C21,0)),ROW(A3:A21)-ROW(A3)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I never use PTs.

Let me see if I can get a PT advocate to look at this.

The attached workbook doesn't include an actual pivot table, just a regular table with a filter applied. I could work on that, but it may be wasted effort since it's not a real pivot table, no real pivot tools.

Can you post a workbook with real starting data so we can pivot it correctly? You can include a manual mockup of the results you're wanting from that raw data.

Originally Posted by pattheriault

But Helper column doesn't change to apply different filter.

For example?

So, forgetting the filter, you want a formula that will show 1 the first time it shows in VISIBLE rows, and a 0 if it shows again in VISIBLE rows. If the VISIBLE rows change, you want the formula results to change so the first VISIBLE row for the guy will show 1 even if it's not the same row as it was before? If yes, I'm thinking you will need a SUBTOTAL trick of some kind.

Hi.

Bit confused. So can you clarify whether or not this is to form part of a Pivot Table?

Regards

Here is my way...
I have used named ranges and Frequency function

Named Ranges by selecting any cell in first row
DataArray

Formula:

=IF((Table1[job number]=Sheet1!$F4)*(Table1[helper column]>0),MATCH(Table1[task code],Table1[task code],0))

BinArray

Formula:

=IF((Table1[job number]=Sheet1!$F4)*(Table1[helper column]>0),MATCH(Table1[task code],Table1[task code],0),0)

Formula in G3

Formula:

=SUM(FREQUENCY(DataArray,BinArr))

I have explained this frequency construction in Post No. 39 in the link below:-
http://www.excelforum.com/excel-form...ml#post3958135

Check the attached file:-
Example__1.xlsx