+ Reply to Thread
Results 1 to 8 of 8

Help counting occurrence of partial number matches in column of numbers, based on criteria

  1. #1
    Registered User
    Join Date
    11-16-2016
    Location
    Irving, TX
    MS-Off Ver
    2007
    Posts
    4

    Help counting occurrence of partial number matches in column of numbers, based on criteria

    Hello All,

    I am having difficulty using a CountIf function to search for a partial invoice number, subject to the Invoice's Due Date and Invoice Status (Past Due or Due Soon).

    I have a list of invoice numbers beginning in Row 7 of Column B and their respective Due Dates and Invoice Status in Columns D and F, respectively.

    I would like to count the number of results of the search, according to the Date Range (Cells B2 and C2), for invoice numbers containing "123" and that have a "Past Due" Invoice Status, and display the number in cell F5.

    Please let me know where I an further clarify any questions. Any assistance is greatly appreciated. Thank you!

    SampleInvoice.JPG


    Start End
    DUE DATE RANGE 11/20/2016 12/20/2016

    Invoice # Search: 123 Invoice Status: PAST DUE Search Results: Return the number of results matching the criteria here

    Customer Invoice Number Invoice Date Due Date Invoice Amount Invoice Status
    CustomerA 123123 10/15/2016 11/14/2016 $5,000.00 PAST DUE
    CustomerB 123223 11/26/2016 12/26/2016 $7,500.00 DUE SOON
    CustomerB 223224 10/27/2016 11/26/2016 $10,000.00 PAST DUE
    CustomerC 123323 11/28/2016 12/28/2016 $50,000.00 DUE SOON
    CustomerA 123124 10/29/2016 11/28/2016 $8,000.00 PAST DUE
    CustomerA 123125 11/30/2016 12/30/2016 $16,000.00 DUE SOON
    Attached Files Attached Files
    Last edited by rhogsett; 12-06-2016 at 02:41 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    Hi, welcome to the forum

    Containing 123 or starting with 123?
    Will there only ever be 1 match? (doesnt look like it from the sample)
    what would a sample answer look like?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-16-2016
    Location
    Irving, TX
    MS-Off Ver
    2007
    Posts
    4

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    Hi! Thank you for responding.

    The formula would apply to Containing "123" (or whichever partial invoice number sequence is entered).

    You are correct, there are likely to be many more than one match, based on what is entered in cell B4.

    A sample answer would simply display "1" in cell F1 based on the currently entered criteria of: Invoices containing "123", due between 11/20/16 and 12/20/16, with Invoice Status of "Past Due".

    Thank you,

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    And you are using 2007 version, correct?
    (countifS() is not included in that version)

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    And you are using 2007 version, correct?
    (countifS() is not included in that version)

  6. #6
    Registered User
    Join Date
    11-16-2016
    Location
    Irving, TX
    MS-Off Ver
    2007
    Posts
    4

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    Correct, 2007 version. COUNTIFS() are available.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    Sorry for the delay, I was afk
    Try this...
    =SUMPRODUCT(--ISNUMBER(SEARCH($B$4,$B$7:$B$12)),--($F$7:$F$12="past due"),--($D$7:$D$12>=$B$2),--($D$7:$D$12<=$C$2))

  8. #8
    Registered User
    Join Date
    11-16-2016
    Location
    Irving, TX
    MS-Off Ver
    2007
    Posts
    4

    Re: Help counting occurrence of partial number matches in column of numbers, based on crit

    Worked perfectly, thank you so much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-22-2014, 04:17 AM
  2. Counting the number of times a partial value appears in a column
    By aa94 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2014, 09:37 AM
  3. Replies: 4
    Last Post: 06-08-2013, 01:52 PM
  4. Counting the occurrence of 2 Criteria on a row in a workbook
    By Chris_Excel_Amateur in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-21-2012, 06:13 PM
  5. [SOLVED] Counting digit occurrence in number/column
    By tazdingomon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 02:35 AM
  6. counting names in column and matches phone numbers
    By Duckie in forum Excel General
    Replies: 8
    Last Post: 12-09-2009, 08:23 AM
  7. Counting occurrences in one column based on an occurrence in anoth
    By Jim Jackson in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-03-2006, 07:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1