Count the number of unique names per week

[PHolt]

Hi,

I have a worksheet listing data showing work completed by staff for a period of serveral weeks.

Daily sheet (Data)
Name in Column A
Date in Column B
Week Number in Column C

Weekly sheet
Week commencing in Column A
Week number in Column B


I need a formula to count the number of unique names per week.

I have been able to get the formula to count the names for the whole sheet, but when ever this formula calculates my excel freezes

=SUMPRODUCT((Data!A:A<>"")/COUNTIF(Data!A:A,Data!A:A&""))

I have tried using, but my Excel froze so I don't know if this formula works.

=COUNTIF(Data!C:C='Weekly Data'!B4,SUMPRODUCT((Data!A:A<>"")/COUNTIF(Data!A:A,Data!A:A&"")),"")

Can some one help with a formula that won't freeze my excel.

Any help will be appreciated</SPAN></SPAN>.

Thanks
Pam

 

[RonB1111]

Try not using the entire column when using SUMPRODUCT - ie: use A2:A100 or whatever rows actually contain data, rather than A:A.

Assuming your headers in each sheet are on row 1 and the data starts on row 2, try this formula in the Weekly sheet cell C2 and copy down (adjust the last row 100 in the formula to your actual ending data row):
=SUMPRODUCT(1/COUNTIFS(Data!A$2:A$100,Data!A$2:A$100,Data!C$2:C$100,Data!C$2:C$100),--(Data!C$2:C$100=Weekly!B2))

If you want the formula to extend beyond the end of your data, I'd suggest filling the Data sheet blank cells with 0's rather than trying to exclude them in the countif/sumproduct formula.

 

[PHolt]

Thank you