- Create a constituent query with a Gift Date between the appropriate dates (example: 1/1/2001 through 12/31/2006). Save the query.
- Create a constituent export based on this query.
- Select Total Number of Gifts and enter a date range of 1/1/2001 to 12/31/2001.
- Repeat for each year until there are five separate fields. To set different criteria, highlight Summary Information on the right side of the screen before selecting the field again.
- Add other export fields as necessary (such as name or addressee).
- Export into comma-separated values format and open the exported file in Excel.
- Insert the cursor in the column after the last year of giving.
- Select Insert, Function from the menu bar. Choose COUNTIF. In the Range box, enter the appropriate cell range. (For example, if year 1 starts in column B2 and year 5 is in column F2, then enter B2:F2). Enter >0 as the Criteria to skip any years the constituents did not donate.
- Copy and drag the formula down the column.
- The number displayed is the number of years in which they have gifts.
- Sort the file by the formula column. To see only constituents who gave at least four out of the last five years, delete any constituents who do not have a 4 or 5 listed in this column.
Connect and collaborate with fellow Blackbaud users.