Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •