Recency/Frequency grid using COUNTIF

Posted by Neil on June 16, 2001 1:24 AM

I am trying to produce a grid showing the recency and frequency of my customers visits to my business.

The formula I am having trouble with is:-

=COUNTIF((TranCount,">=10") AND(NOW() - (DateLastTran-1)"&LT;=7"))

which will count those customers who have visited more than 10 times and have called within the last week

I am using Excel 97

Thanking you in advance

Neil

Posted by Aladin Akyurek on June 16, 2001 1:45 AM

Neil

Select an unused cell, type =, select 10 to 15 rows of your data including the column headings/labels, then hit control+shift+enter. Go to the formula bar. You'll see there a range between { and }. Select the formula and hit F9. Copy what you see and post it in the follow-up.

Aladin

Posted by Neil on June 16, 2001 7:44 PM

Posted by Neil on June 16, 2001 7:53 PM

Aladin

Thanks for the quick response

The data you requested is:

{"DateLastTran", "TotalTrans", "TranCount";37057, 311, 28;37057, 857.05, 27;37057, 731.28, 24;37056, 37.4, 22;37056, 142.94, 20;37051, 485.23, 17;37057, 449.55, 17;37057, 301.57, 17;37057, 302.05, 16;37057, 173.24, 16;37056, 412.45, 15;37057, 181.2, 15;37055, 161.2, 15;37057, 403.84, 14}

I have arrays labeled by their column heading and have no trouble using

=COUNTIF(TranCount,">=10")

but run into trouble when introducing the 'AND' to get a recency reading

Thanks again

Neil

Posted by Aladin Akyurek on June 17, 2001 12:05 AM

Neil,

If the data are of a single customer, you'll get a count of this customer's visits of the last 7 days where TranCount >=10.
If the data represents multiple customers, you'll get a count of customers who visited your business within the last 7 days and whose TranCount>=10.

Array-enter the following formula

=SUM((A2:A15>=TODAY()-7)*(C2:C15>=10))

where A2:A15 contains dates, C2:C15 transaction counts.

Note. In order to array-enter a formula you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

If you'd prefer using ordinary formulas, do the following:

In D2 enter: =(A2>=TODAY()-7)+0 [ copy down as far as needed ]
In E2 enter: =(C2>=10)+0 [ copy down as far as needed ]

Then use the following formula to get the count of interest:

=SUMIF(E2:E15,1,F2:F15)

Aladin

========================= The data you requested is: {"DateLastTran", "TotalTrans", "TranCount";37057, 311, 28;37057, 857.05, 27;37057, 731.28, 24;37056, 37.4, 22;37056, 142.94, 20;37051, 485.23, 17;37057, 449.55, 17;37057, 301.57, 17;37057, 302.05, 16;37057, 173.24, 16;37056, 412.45, 15;37057, 181.2, 15;37055, 161.2, 15;37057, 403.84, 14}