Non Profit needs help with donor categories

[Hucbald]

Hi--I need help with writing thank you letters for donors. As a donation comes in, we enter the amount next to the persons name. What I need is a formula that totals up all the donations--sometimes it's just one per name, sometimes two or three--and then sorts the totals into ranges--$100-$249 $250-$499 above $500.
The names would then, ideally, be sorted into these categories in alphabetical order by last name--
is it possible to do this in excel? It sure would save us a lot of time, tx.

 

[Eric W]

Welcome to the forum.

You could do something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Donor	Amount	AAA	Total	<$100	$100-$249	$250-$499	>$499
Doe, John	Andrews, Julie	Mouse, Mickey	Doe, John	Cugat, Xavier	Andrews, Julie
Smith, Jane	Cugat, Xavier			Klein, Calvin			Simpson, Homer
Andrews, Julie	Doe, John			Smith, Jane
Doe, John	Klein, Calvin
Klein, Calvin	Mouse, Mickey
Andrews, Julie	Simpson, Homer
Mouse, Mickey	Smith, Jane
Cugat, Xavier
Cugat, Xavier
Simpson, Homer

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 750.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 150.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 250.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 750.00 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"] $ 240.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 250.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"] $ 150.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"] $ 1,000.00 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] $ 350.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 150.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"] $ 240.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] $ 100.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 150.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] $ 150.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] $ 400.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 1,000.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] $ 50.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 240.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] $ 125.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] $ 125.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] $ 1,000.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2="","",SUMIF($A:$A,D2,$B:$B))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=IF(J2="","",SUMIF($A:$A,J2,$B:$B))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=IF(M2="","",SUMIF($A:$A,M2,$B:$B))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Q2[/TH]
[TD="align: left"]=IF(P2="","",SUMIF($A:$A,P2,$B:$B))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(G2="","",SUMIF($A:$A,G2,$B:$B))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($A$2:$A$100,"<"&$A$2:$A$100)-SUM(COUNTIF($A$2:$A$100,"="&D$1:D1)),0)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]{=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100>=100)*($E$2:$E$100<250),ROW($E$2:$E$100)),ROWS(J$2:J2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]{=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100>=250)*($E$2:$E$100<500),ROW($E$2:$E$100)),ROWS(M$2:M2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P2[/TH]
[TD="align: left"]{=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100>=500),ROW($E$2:$E$100)),ROWS(P$2:P2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100<100),ROW($E$2:$E$100)),ROWS(G$2:G2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The donor list is in column A, amounts in column B. Put the headers in columns D:P.

Then put the formula in D2, change the bottom row from 100 to the maximum you think you'll need, then confirm the formula with Control+Shift+Enter. Then put the formula in E2, with enter only. Then drag D2:E2 down the column. This generates a unique sorted list with the total amount per person.

Next, put in the G2 formula with Control+Shift+Enter, and the H2 formula with enter, then drag those down as far as needed. Repeat with J2:K2, M2:N2, and P2:Q2.

Your list should automatically update as you enter names in column A.

Let me know if this works for you.

 

[Hucbald]

Thank you so much for this information, I will dive in over the weekend! It looks over my head but I think if I just follow your examples I can figure it out, thanks again.

 

[Eric W]

Array formulas can be tricky, just keep track of the ranges in the formulas, and adjust to work with your sheet. Let us know if you need additional help.

Good luck!