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]

	A​	B​	C​	D​	E​	F​	G​
1​	North America	Donor	A	1/9/2017​			9/30/2017​
2​	North America	Donor	B	3/31/2017​
3​	North America	Donor	E	8/12/2017​			donator
4​	North America	Donor	T	2/28/2017​			7​
5​	North America	Donor	A	4/27/2017​
6​	North America	Donor	B	6/24/2017​
7​	North America	Donor	M	7/8/2017​
8​	North America	Donor	C	9/15/2017​
9​	North America	Donor	C	10/1/2017​
10​	North America	Donor	A	3/4/2017​
11​	North America	Donor	C	5/15/2017​
12​	North America	Donor	C	10/2/2017​
13​	North America	Donor	P	3/20/2017​
14​	North America	Donor	Z	10/5/2017​

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]

	A​	B​	C​	D​	E​	F​	G​
1​	North America	Donor	A	1/9/2017​			9/30/2017​
2​	North America	Donor	B	3/31/2017​
3​	North America	Donor	E	8/12/2017​			donator
4​	North America	Donor	T	2/28/2017​			7​
5​	North America	Donor	A	4/27/2017​
6​	North America	Donor	B	6/24/2017​
7​	North America	Donor	M	7/8/2017​
8​	North America	Donor	C	9/15/2017​
9​	North America	Donor	C	10/1/2017​
10​	North America	Donor	A	3/4/2017​
11​	North America	Donor	C	5/15/2017​
12​	North America	Donor	C	10/2/2017​
13​	North America	Donor	P	3/20/2017​
14​	North America	Donor	Z	10/5/2017​

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