Count Unique Text Value with multiples criterias

[CrazyBella]

Hello. I need to count the number of donors who have contributed as of 09/30/2017 in the North America region Only. How can I do it? I am using the frequency and match formulas, but I don't know how to use it with multiple criteria, such as, dates and region. I have this data,

Region/ Location Donor Donor Name Date on Books
North America Donor A 1/9/2017
North America Donor B 3/31/2017
North America Donor E 8/12/2017
North America Donor T 2/28/2017
North America Donor A 4/27/2017
North America Donor B 6/24/2017
North America Donor M 7/8/2017
North America Donor C 9/15/2017
North America Donor C 10/1/2017
North America Donor A 3/4/2017
North America Donor C 5/15/2017
North America Donor C 10/2/2017
North America Donor P 3/20/2017
North America Donor Z 10/5/2017

 

[jkpieterse]

Easiest is to create a pivot table which has region, Donor and date as row fields and any other column as sigma field. THen you can easily filter to get the information you need.

 

[MARZIOTULLIO]

[Table="width:, class:grid"][tr][td][/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

1/9/2017​

[/td][td][/td][td][/td][td]

9/30/2017​

[/td][/tr]

[tr][td]

2​

[/td][td]North America[/td][td]Donor[/td][td]B[/td][td]

3/31/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]North America[/td][td]Donor[/td][td]E[/td][td]

8/12/2017​

[/td][td][/td][td][/td][td]donator[/td][/tr]

[tr][td]

4​

[/td][td]North America[/td][td]Donor[/td][td]T[/td][td]

2/28/2017​

[/td][td][/td][td][/td][td]

7​

[/td][/tr]

[tr][td]

5​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

4/27/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]North America[/td][td]Donor[/td][td]B[/td][td]

6/24/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]North America[/td][td]Donor[/td][td]M[/td][td]

7/8/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

9/15/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

10/1/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

3/4/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

5/15/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

10/2/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]North America[/td][td]Donor[/td][td]P[/td][td]

3/20/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]North America[/td][td]Donor[/td][td]Z[/td][td]

10/5/2017​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

g4=SUM(IF(FREQUENCY(IF($A$1:$A$14=$A$1,IF(D1:D14<=$G$1,MATCH($C$1:$C$14,$C$1:$C$14,0))),ROW($C$1:$C$14)-ROW(C1)+1),1))

control+shift enter

 

[CrazyBella]

Thank you very much !! This helps a lot!

 

[CrazyBella]

[Table="width:, class:grid"][tr][td][/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

1/9/2017​

[/td][td][/td][td][/td][td]

9/30/2017​

[/td][/tr]

[tr][td]

2​

[/td][td]North America[/td][td]Donor[/td][td]B[/td][td]

3/31/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]North America[/td][td]Donor[/td][td]E[/td][td]

8/12/2017​

[/td][td][/td][td][/td][td]donator[/td][/tr]

[tr][td]

4​

[/td][td]North America[/td][td]Donor[/td][td]T[/td][td]

2/28/2017​

[/td][td][/td][td][/td][td]

7​

[/td][/tr]

[tr][td]

5​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

4/27/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]North America[/td][td]Donor[/td][td]B[/td][td]

6/24/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]North America[/td][td]Donor[/td][td]M[/td][td]

7/8/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

9/15/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

10/1/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]North America[/td][td]Donor[/td][td]A[/td][td]

3/4/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

5/15/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]North America[/td][td]Donor[/td][td]C[/td][td]

10/2/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]North America[/td][td]Donor[/td][td]P[/td][td]

3/20/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]North America[/td][td]Donor[/td][td]Z[/td][td]

10/5/2017​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

g4=SUM(IF(FREQUENCY(IF($A$1:$A$14=$A$1,IF(D1:D14<=$G$1,MATCH($C$1:$C$14,$C$1:$C$14,0))),ROW($C$1:$C$14)-ROW(C1)+1),1))

control+shift enter

This was great ! Thanks.

 

[MARZIOTULLIO]

g4=SUM(IF(FREQUENCY(IF($A$1:$A$14=$A$1,IF(D1:D14<=$G$1,MATCH($C$1:$C$14,$C$1:$C$14,0))),ROW($C$1:$C$14)-ROW($C$1)+1),1))

control +shift+enter

sorry i forgot double dollars ($C$1) for C1