1. Create a constituent query with a Gift Date between the appropriate dates (example: 1/1/2001 through 12/31/2006). Save the query.
  2. Create a constituent export based on this query.
  3. Select Total Number of Gifts and enter a date range of 1/1/2001 to 12/31/2001 for each year you are considering
  4. 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.
  5. Add other export fields as necessary (such as name or addressee).
  6. Export into comma-separated values format and open the exported file in Excel.
  7. Insert the cursor in the column after the last year of giving.
  8. Select Insert, Function from the menu bar. Choose COUNTIF function in excel. For example type: =COUNTIF(B2:D2,“>0”)
User-added image
  1. 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,">0") as the Criteria to skip any years the constituents did not donate.
  2. Copy and drag the formula down the column.
  3. The number displayed is the number of years in which they have gifts.
  4. 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.