Summary of largest annual donation

I'm a bit late to the party here - did you manage to resolve this in the end? The closest canned report I can see might be the First/Greatest/Latest Gift Report, but that seems to only look at individual gifts rather than a summary per year.


The only thing that springs to mind would be to do a pivot report based on a query of all gifts, with their dates, amounts and constituent IDs, so you end up with a row per constituent and a column per year. If you want to use financial years rather than calendar years, you might need to add an extra column to your data with a formula to determine which year a date falls into.


Once you've got a summary of giving by year, you can then use a combination of the Index, Match and Max functions to determine in which year each person gave the most. For example, if I had the below data in columns A:E, the formula =INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0)) would tell me in which year donor 123456 gave the most.

(Happy to step through the formula in more detail, but basically it's using a Max to determine what the maximum amount was, then using a Match to determine which column that was in, then using an Index to retrieve the year from row 1 of the same column)


Waiting for someone to come along and tell me there's a canned report that does all this automatically! ?