MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)"<=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}