• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique values based on more criteria

V

Villalobos

Active Member
Hello, I would like to count the unique values based on more criteria.

Here is my list:

Name Parties Dates
Salesman #1 A 2013.09.01
Salesman #1 B 2013.09.01
Salesman #1 C 2013.09.23
Salesman #2 B 2013.09.23
Salesman #2 B 2013.09.02
Salesman #2 F 2013.08.31
Salesman #2 A 2013.08.31
Salesman #2 D 2013.06.01
Salesman #2 D 2013.06.01
Salesman #2 E 2013.13.12
Salesman #3 C 2013.08.31
Salesman #3 A 2013.08.31
Salesman #3 A 2013.07.05
Salesman #3 B 2013.09.15

Here is that result what I would like to see (in column "uniqe parties"):

Name Criteria Uniques parties Explanation to unique parties
Salesman #1: Number of unique parties before today: 2 (A+B)
Salesman #2: Number of unique parties before today: 4 (B+F+A+D)
Salesman #3: Number of unique parties before today: 3 (C+A+B)

Could somebody help me in this issue?
 
Hi, Villalobos!
Try this:
=CONTAR.SI.CONJUNTO(A$2:A$15;E2;C$2:C$15;"<"&HOY()) -----> in english: =COUNTIFS(A$2:A$15,E2,C$2:C$15,"<"&TODAY())
Regards!
PS: BTW, you have 2 errors:
a) Date error for #2 / E: 2013.13.12 isn't a valid date according to the column format
b) Count error for #3: it should be 4 instead of 3
 
Hello SirJB7,

Yes, I have made mistake... little bit late for me...
Are you from Spain? :)

I have problem with your formula.
The formula does not take into account the unique values in column "Parties, only the dates.
 
Hi, Villalobos!
Sorry, you're right, I'll fix it and post again.
Regards!
PS: I'm from Argentine ;)
PS2: Clicking on any member nick you could access to his public information.
 
Hello Villalobos,
Try the following:
=SUM(SIGN(FREQUENCY(IF((slist=G2)*(sdates<H2), MATCH(clist,clist,0)), ROW(OFFSET(A$1,,,ROWS(clist))))))

enter with Ctrl + Shift + Enter
where slist is the range for your sales people
clist is the range for the values such as A,B, etc.
sdates is the range for the dates
G2 refers to the sales person you are doing the count for
H2 refers to the date you are comparing to

Cheers,
Sajan
 
Hi, Villalobos!
Job done. And it didn't cost me any effort :)
Regards!

@Sajan
Hi!
Thanks, and do my job whenever you want ;)
Regards!
 
Hi Guys,

I would have one more question. How should I modify the formulae if I would like that, the formulae take into account that rows, if the column Filter contain the "Yes" word?

Filter Name Parties Dates
Yes Salesman #1 A 2013.09.01
No Salesman #1 B 2013.09.01
Yes Salesman #1 C 2013.09.23
No Salesman #2 B 2013.09.23
Yes Salesman #2 B 2013.09.02
Yes Salesman #2 F 2013.08.31
No Salesman #2 A 2013.08.31
No Salesman #2 D 2013.06.01
Yes Salesman #2 D 2013.06.01
Yes Salesman #2 E 2013.12.13
Yes Salesman #3 C 2013.08.31
No Salesman #3 A 2013.08.31
No Salesman #3 A 2013.07.05
No Salesman #3 B 2013.09.05
 
Hi villalobos,
You can extend the following section of the formula to add more conditions:
(slist=G2)*(sdates<H2)

For example, you can have (slist=G2)*(sdates<H2)*(Filter="Yes")

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top