Subtotaling Working unique Days

**Sultix** · 12-30-2011, 06:17 PM

Hi,

i attached a sample.. could you please help me.. i cant seem to figure out the formula..

i want to count only the working days from List, say if i have same date 5 or 10 times in range, it should counted as one day.
i also tried count or counta formulas, didnt seem to work.

I need a solution for subtotal.. if i filter a month or different criteria it should show at the top the counted days..
also the pivot dont seem to work aswell..

Thanks for helping me

happy new year

**Palmetto** · 12-30-2011, 08:24 PM

On Sheet1, add this formula into a helper column (call it Unique Count or whatever)

=IF(COUNTIF($B$4:B4,B4)>1,0,COUNTIF($B$4:B4,B4))

Copy it down the column

Reset the Pivot Table to include the new column, then add the Unique Count to the Values field and change the field setting to SUM (not count).

Or to use the SUBTOTAL function with the filter on sheet1,

=SUBTOTAL(9,J4:J223)

Where column-J is the helper column

**alansidman** · 12-30-2011, 08:32 PM

Look at this thread in another forum for a solution.

http://www.mrexcel.com/forum/showthread.php?t=70835

**dilipandey** · 12-31-2011, 01:38 AM

Hi sultix,

A very happy new year to you and your loved ones .. cheers

See the attached file where I have given a try solving your query. Let me know if this helps.

Regards,
DILIPandey

<click on below 'star' if this helps>

**XLent** · 12-31-2011, 01:55 AM

I would suggest:

1 - add a SUBTOTAL for each line on your table (hide column as necessary)

K4: =SUBTOTAL(2,B4)
copied down to K223

2 - use a single SUMPRODUCT with COUNTIFS (possible given your version)

E1: =SUMPRODUCT($K$4:$K$223/COUNTIFS($K$4:$K$223,$K$4:$K$223&"",$B$4:$B$223,$B$4:$B$223&""))

The above would generate 13 | 8 | 9 for Sep, Oct & Nov respectively - and would work regardless of which filters you apply (i.e not exclusive to month selection).

**Haseeb A** · 01-01-2012, 07:16 PM

This Array Formula will give the count of unique in filtered range.

=SUM(SIGN(FREQUENCY(IF(SUBTOTAL(2,OFFSET(B4,ROW(B$4:B$223)-ROW(B4),)),B$4:B$223),B$4:B$223)))

Or, as Palmetto said, use this formula in a helper column & use as SUBTOTAL(9,...) also add this column in pivot table.

=(COUNTIF(B$4:B4,B4)=1)+0

**Sultix** · 01-06-2012, 08:17 PM

Hi fellow contributors,

all four methods works perfectly.. i have now so many different methods for different methods or results

i never thought about helper column, there is also another way with =IF(B4=B5;0;1) in helper column, and then sum it with subtotal formula.

thanks for all your help..

@haseeb, your formula looks similar to dilipantey, i will try that tomorrow