# Thread: Need help with a frequency formula

1. ## Need help with a frequency formula

Hi! I need a formula that I can use to get the count of a sol, by LOB, using a date range. Below is my data table. I need a formula that for the month of May, can tell me that there are 2 SOLs (12797 & 12801) for LOB = MAINSTREET. I would like to use a >=&, etc. for the date range in the formula. Any help is appreciated! I am not sure if an array frequency formula would do the trick.

Date LOB SOL CELL QUANTITY
04/15/14 UPMARKET 12750 1 15,000
04/15/14 UPMARKET 12750 2 25,000
04/15/14 UPMARKET 12750 3 39,000
04/15/14 UPMARKET 12750 4 40,000
04/15/14 UPMARKET 12750 5 35,000
04/22/14 MAINSTREET 12775 1 10,000
04/22/14 MAINSTREET 12775 2 17,000
04/22/14 MAINSTREET 12775 3 20,000
04/22/14 MAINSTREET 12775 4 25,000
04/22/14 MAINSTREET 12775 5 30,000
05/01/14 MAINSTREET 12797 1 50,000
05/01/14 MAINSTREET 12797 2 75,000
05/01/14 MAINSTREET 12797 3 25,000
05/01/14 MAINSTREET 12797 4 10,000
05/01/14 MAINSTREET 12797 5 5,000
05/01/14 MAINSTREET 12797 6 7,500
05/01/14 MAINSTREET 12797 7 12,000
05/01/14 MAINSTREET 12797 8 20,000
05/12/14 UPMARKET 12800 1 5,000,000
05/24/14 MAINSTREET 12801 1 10,000
05/24/14 MAINSTREET 12801 2 15,000
05/24/14 MAINSTREET 12801 3 7,500
05/24/14 MAINSTREET 12801 4 8,000
05/24/14 MAINSTREET 12801 5 15,000
05/24/14 MAINSTREET 12801 6 20,000
06/01/14 MAINSTREET 12815 1 50,000
06/01/14 MAINSTREET 12815 2 35,000
06/01/14 MAINSTREET 12815 3 40,000
06/01/14 MAINSTREET 12815 4 50,000
06/01/14 MAINSTREET 12815 5 25,000
06/01/14 MAINSTREET 12815 6 30,000

2. How about an array formula (confirm with ctrl-shift-enter) [Adj range as needed]:
Code:
`=SUM(IF((B2:B32="MAINSTREET")*(A2:A32>=DATE(2014,5,1))*(A2:A32<DATE(2014,6,1)),1/COUNTIFS(B2:B32,"MAINSTREET",A2:A32,">="&DATE(2014,5,1),A2:A32,"<"&DATE(2014,6,1),C2:C32,C2:C32)))`
Steve