Count of unique values occuring in a year AND subsequent year

[Sam_NY]

I have a table of donations given over several years, and need to generate a count for how many first time donors in a given year also gave in the next year (note I need the count of unique donors, not the count of gifts, which may duplicate).

Thanks to some help received yesterday (h/t sheetspread), I have a column (GiftNumber) indicating the ordinal place of that gift by date (so 1 = first gift, 2 = second, etc.):

FY donorID Gift Amt FirstGift GiftNumber
2018 111 100 1 1
2019 222 200 0 2
2019 111 100 0 2
2019 111 500 0 3
2018 222 50 1 1

So really I want a formula outside of the table that =2 if I'm looking at first time donors in FY18 (since donors 111 and 222 gave their 1st gift in FY18 (row 2 and 6) and ALSO give a gift in FY19.

I thought I could do something using FREQUENCY with a bunch of conditions that capture donors that gave first gift in 2018 and some gift in 2019:

=SUM(--(FREQUENCY(IF(conditions,Table1[donorID]),Table1[donorID])>0))

but I'm not sure how to structure the conditions or if I'm completely barking up the wrong tree. Any help appreciated!

 

[C Moore]

If you don't mind adding another column and a year condition cell (I named the cell in my example and used 2018) you could do the following:
=SUMPRODUCT(--([@donorID]&[@FY]+1=[donorID]&[FY]),--([FY]=yrcond+1)) Ctrl+Shift+Enter

That column would be the total gifts given in a subsequent year, 222 gave 1 and 111 gave 2. Your outside formula would be:
=COUNTIF(Table1[test2],">0")

 

[Aladin Akyurek]

@ Sam_NY

Book1
	A	B	C	D	E	F	G
1	FY	donorID	Gift Amt	FirstGift	GiftNumber
2	2018	111	100	1	1		2
3	2019	222	200	0	2
4	2019	111	100	0	2
5	2019	111	500	0	3
6	2018	222	50	1	1
Sheet1

In G2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(IF($A$2:$A$6=2019,$B$2:$B$6,""),IF($A$2:$A$6=2018,$B$2:$B$6),0)),$B$2:$B$6),$B$2:$B$6),1))