1

I work for a small charity, and I'm trying to write a formula which will tell me each month how many unique individuals have donated in the last two years. A sample set of data is below, but the full data set is over 10 000 rows and is formatted as a table (because it makes it easier to calculate several of the other metrics in the workbook). The constituent ID is a unique identifier for donors.

So far, I've tried the following (the dates in question being 31st May 2018 and 30th April 2016, although in reality I'm using cell references):

=SUM(--(FREQUENCY(IF((Table[Payment Date]<=43251)*(Table[Payment Date]>42521),Table[Constituent ID]),Table[Constituent ID])>0))

Unfortunately, the formula doesn't work. The Frequency function is always evaluating to {0,0} and I can't work out why. Any help?

Constituent ID  Constituent Codes   Approved Amount Payment Date
1010251         Sponsor             12              12/07/2018
1010298         Supporter           25              10/07/2018
1010282         Supporter           75.35           10/07/2018
1002509         Supporter           10              10/07/2018
1002881         Supporter           100             09/07/2018
1003481         Supporter           5               09/07/2018
1005692         Supporter           10              09/07/2018
1008573         Supporter           50              09/07/2018
1003940         Supporter           5               09/07/2018
1007763         Supporter           100             09/07/2018
1010279         Supporter           10              07/07/2018
1008885         Supporter           5               07/07/2018
Pierre.Vriens
  • 1,425
  • 38
  • 16
  • 20
user925644
  • 11
  • 1
    (1) Perhaps FREQUENCY is returning zeroes because you have no data in your table that lie within the date range you’re looking for?  (You’re looking for dates ≤ 31st May 2018, but the earliest date in your table is 07/07/2018.)  (2) 30th April 2016 through 31st May 2018 is 25 months, not two years.  (3) Please don’t post dates in the format nn/nn/nnnn.  If you must, at least make it clear whether they are mm/dd/yyyy or dd/mm/yyyy. – Scott - Слава Україні Jul 20 '18 at 04:47
  • @Scott The serial value 42521 is actually 31st May 2016, which gives the correct two year interval. – robinCTS Jul 20 '18 at 11:43
  • Not sure what you're doing wrong with your real formula, but once you adjust the hard-coded serial values to match those in your sample data (I used DATEVALUE("2018-07-12") and DATEVALUE("2018-07-07")), your test formula works just fine, provided you array enter (Ctrl+Shift+Enter) it. (Also, the {0,0} result for the FREQUENCY() function makes no sense at all. There should be way more elements to the array for your full data set.) – robinCTS Jul 20 '18 at 12:37

1 Answers1

0

I posted the question originally (I can't work out how to log in as the same user again). I found a solution:

=SUM(--(FREQUENCY(IF((Table[Payment Date]<=DATEVALUE("2018-05-31"))*(Table[Payment Date]>DATEVALUE("2016-05-31")),--(Table[Constituent ID])),--(Table[Constituent ID]))>0))

My Constituent IDs were stored as text, so they just needed to be converted to numbers.

user925986
  • 1