Table column showing earliest instance indicator

[Sam_NY]

New to forum! Hello and thanks!

I'm trying put a column in a table of donations that indicates whether or not its the earliest donation ("FirstGift") from that individual donor. Or, better yet, have a column return whatever place each gift is ("GiftNumber") with 1 being the first/earliest gift, 2 the second, etc. Donor IDs may duplicate. Here's the sample table I'm working with (2 right-most cols are my desired output):

Date donorID Gift Amt FirstGift GiftNumber
6/29/2018 111 100 1 1
7/2/2018 222 200 0 2
7/1/2018 111 100 0 2
7/2/2018 111 500 0 3
6/29/2017 222 50 1 1

I'd like to do this with formulas, not VBA. I've gotten as far as getting the date of the earliest gift inserted in each cell using this as a ctrl + shift + enter formula: =MIN(IF([donorID]=[@donorID],[Date]))

And I suspect that doing something like this within an INDEX/MATCH formula would do it, but I'm new to CSE formulas in general and INDEX/MATCH specifically and can't seem to wrap my head around how to do it for FirstGift (much less GiftNumber). Appreciate any help on either method.

 

[sheetspread]

This works if you sort by date:

Excel 2010
	A	B	C	D	E
1	Date	donorID	Gift	FirstGift	GiftNumber
2	6/29/2017	222	50	1	1
3	6/29/2018	111	100	1	1
4	7/1/2018	111	100	0	2
5	7/2/2018	222	200	0	2
6	7/2/2018	111	500	0	3
Sheet5

Cell Formulas
Range	Formula
E2	=COUNTIF($B$2:B2,B2)

 

[Sam_NY]

Thanks, sheetspread. Is there a way method that doesn't require sorting first? I guess I could even do an IF() function with a sorted list - though yours is better - but would prefer something where the rankings stay the same regardless of sorting/filtering.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E
1	Date	donorID	Gift	FirstGift	GiftNumber
2	6/29/2018	111	100	1	1
3	7/2/2018	222	200	0	2
4	7/1/2018	111	100	0	2
5	7/2/2018	111	500	0	3
6	6/29/2017	222	50	1	1
Sheet5

Cell Formulas
Range	Formula
E2	=COUNTIFS($B$2:$B$6,B2,$A$2:$A$6,"<="&A2)

 

[Sam_NY]

Yup, that works exactly how I want! Thanks very much. If it's simple to explain I'd love to know how this works: "<="&A2

I would think that would yield a string, but it's obviously just serving as a condition. And I'm guessing <=A2 wouldn't work. Why on earth would the operator go in quotes?

 

[sheetspread]

I'm not sure why the countif syntax is so strict: constants must be entered as "<=5" and "<="&a2 for a cell reference.