Help with to find donations in one year but not the next.

[NeedInformation]

I'm having a brain cramp with trying to determine the total number (both in dollars and number of unique donors but reported separately) of all donors who gave in the first year but not in the second that live in a certain city. I don't need the donors themselves, just the total of the donations and number of unique donors.

I can sumproduct to find who donated in both. I'm having difficulty in finding who donated in the first year but did NOT donate in the second.

For example if my first year is 2018 and 2017 is the second and looking the City of Dallas I should only find those that are starred which should return a sum of $70 and a count of unique donors as 2

The logic is to find all donations in dallas, then find all who donated in 2018 but not in 2017.

I have a feeling this is a complicated, nested sumproduct but I'm stumped. Any pointers are much appreciated.

All values in any column are not necessarily unique.

DonorID	City	Year	Donation
10	San Antonio	2018	$10
10	San Antonio	2018	$20
20	Dallas	2017	$30
20	Dallas	2018	$40
20	Dallas	2019	$40
40	Dallas	2018	$20*
50	San Antonio	2018	$30
50	Dallas	2017	$30
60	Dallas	2018	$30*
60	Dallas	2018	$20*
60	San Antonio	2018	$50

 

[NeedInformation]

I would think the logic would be something along the lines of;

For a given city:

Array1: Take the array of donors who gave in 2018 (first year) and 2017 (second year)

Array2: Take the array of donors who gave in 2017 (second year)

"Subtract" array 2 from array 1 leaving only those who gave only in 2018. Not sure how to do this.

Using the resultant array add up all the donations to get sum of donations and use the size of the array to get number of donors.

I just can't translate this to Excel-speak.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	DonorID	City	Year	Donation
2	10	San Antonio	2018	10			Year1	Year2	City	Unique Donor in 2018 Not in 2017	Sum
3	10	San Antonio	2018	20			2018	2017	Dallas	2	70
4	20	Dallas	2017	30
5	20	Dallas	2018	40
6	20	Dallas	2019	40
7	40	Dallas	2018	20
8	50	San Antonio	2018	30
9	50	Dallas	2017	30
10	60	Dallas	2018	30
11	60	Dallas	2018	20
12	60	San Antonio	2018	50
Sheet32

Cell Formulas
Range	Formula
J2	J2	="Unique Donor in "&G3&" Not in "&H3
J3	J3	=SUM(SIGN(FREQUENCY(IF(B2:B12=I3,IF(C2:C12=G3,IF(ISERROR(MATCH(A2:A12&"|"&H3,A2:A12&"|"&C2:C12,0)),MATCH(A2:A12,A2:A12,0)))),ROW(A2:A12)-ROW(A2)+1)))
K3	K3	=SUM(IF(B2:B12=I3,IF(C2:C12=G3,IF(ISERROR(MATCH(A2:A12&"|"&H3,A2:A12&"|"&C2:C12,0)),D2:D12))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

This might come up with some confusing results, based on your sample. Donor 50 is in Dallas and San Antonio, so that donor will show up in the totals for both cities.

 

[KRice]

@NeedInformation, thanks for the response and clarification. You cleared up some confusion I had about the criteria.