Code/formulat to count values based on different columns and not including duplicates

[will_raz]

Hello everyone. I could really use some help with this problem I'm having

(Sorry if the image is scaled too large: check the image with the following link if you can't see to column M, or resize screen: http://i.imgur.com/rJYRCPL.png)


What I would like to do is to be able to count unique users (u) per week per host in the image above. I will have a table similar to that on the right where the weeks are displayed and having the "hosts" on the left side. So far the formula I have is the following:

=SUMPRODUCT(COUNTIFS($A:$A,M$1,$D:$D,"Nike"))

This will tell me the amount of times Nike has come up in Week 9. I would like to add further criteria to tell it to only consider unique users within that specific week, for that host.

Users may repeat over different weeks which is why I don't want to remove duplicates based on users (also I'd rather keep all information in my excel for later use). In the example above, in Week 9, it counts 5 times for Nike, but what I would like it to do is count "2" - one for Abe, and one for Will.

This is just a small sample of the data I will be using. My actual excel file will have thousands or rows, but with the same general format.

My goal is to have a macro that will take these values and put it into a table that I will make with weeks on the top and hosts on the left with no formulas behind it. I hope this was clear and that someone can help! If there are any questions or if I wasn't clear please let me know.

Thank you all in advance!

Best,

Will

 

[MUKESHY12390]

for unique list you can use refer this link ... http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/

 

[will_raz]

for unique list you can use refer this link ... How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

I've tried to integrate the index and match combo into my formula but I haven't had much success. Also I only want to get unique users per week (if the same user comes in the following week, I don't want to remove them from that list). Do you have any ideas on how I can get this into my sumproduct/countifs formula? Thanks!