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
1	Donor	Amount		AAA	Total		<$100			$100-$249			$250-$499			>$499
2	Doe, John	$ 50.00		Andrews, Julie	$ 750.00		Mouse, Mickey	$ 50.00		Doe, John	$ 150.00		Cugat, Xavier	$ 250.00		Andrews, Julie	$ 750.00
3	Smith, Jane	$ 240.00		Cugat, Xavier	$ 250.00					Klein, Calvin	$ 150.00					Simpson, Homer	$ 1,000.00
4	Andrews, Julie	$ 350.00		Doe, John	$ 150.00					Smith, Jane	$ 240.00
5	Doe, John	$ 100.00		Klein, Calvin	$ 150.00
6	Klein, Calvin	$ 150.00		Mouse, Mickey	$ 50.00
7	Andrews, Julie	$ 400.00		Simpson, Homer	$ 1,000.00
8	Mouse, Mickey	$ 50.00		Smith, Jane	$ 240.00
9	Cugat, Xavier	$ 125.00
10	Cugat, Xavier	$ 125.00
11	Simpson, Homer	$ 1,000.00

<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>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =IF(D2="","",SUMIF($A:$A,D2,$B:$B)) K2 =IF(J2="","",SUMIF($A:$A,J2,$B:$B)) N2 =IF(M2="","",SUMIF($A:$A,M2,$B:$B)) Q2 =IF(P2="","",SUMIF($A:$A,P2,$B:$B)) H2 =IF(G2="","",SUMIF($A:$A,G2,$B:$B)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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)),"")} J2 {=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))),"")} M2 {=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))),"")} P2 {=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100>=500),ROW($E$2:$E$100)),ROWS(P$2:P2))),"")} G2 {=IFERROR(INDEX($D:$D,SMALL(IF(($D$2:$D$100<>"")*($E$2:$E$100<100),ROW($E$2:$E$100)),ROWS(G$2:G2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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!