+ Reply to Thread
Results 1 to 6 of 6

Countif? Sumproduct?

Hybrid View

  1. 01-10-2019, 06:28 AM #1
    liam_bettinson
    liam_bettinson is offline
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Countif? Sumproduct?

    Hello all, hoping you can help...

    I have a range of data with names in column A and dates across row 1. Within cells B2:R4 I then enter text 'U', 'P' or 'S', with some cells also left blank.

    I need a formula (COUNTIF, SUMPRODUCT perhaps??) to count the number of groups/batches that occur for each name in consecutive cells, but only count the columns on and after the date entered (in the example, only after 5th Jan).

    I have a separate formula to count the individual occurrences of each letter after the date entered, but need to now calculate the occurrence of groups/batches of the letters.

    The batch/cluster table on example shows the expected results.

    Can anybody help? Many thanks!

    Batch count.png
    Last edited by liam_bettinson; 01-10-2019 at 06:59 AM.
    Reply With QuoteRegister To Reply
  2. 01-10-2019, 07:36 AM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Countif? Sumproduct?

    Please upload your sheet, Go Advanced > Manage Attachments.
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 01-10-2019, 07:39 AM #3
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: Countif? Sumproduct?

    to keep things simple... I'd suggest using a Dynamic Named Range relative to your date value such that you don't end up with a hideously convoluted Frequency calc, I'd also suggest storing the row position in which name is found in the data (to reduce repetition)

    With name defined (_Data) the calc would look like:

    Formula: copy to clipboard 
    {=SUM((FREQUENCY(IF(INDEX(_Data,$A10,0)=C$9,COLUMN(INDEX(_Data,1,0))),IF(INDEX(_Data,$A10,0)<>C$9,COLUMN(INDEX(_Data,1,0))))>1)+0)}
    
note: array entry - the { } cannot be applied manually


    the above references are relative to the sample file...

    Note: should your P value for Name 2 not be 1 on grounds 4th Jan irrelevant? (if you change date to 4-Jan in the attached the result will shift to 2)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-10-2019, 07:57 AM #4
    liam_bettinson
    liam_bettinson is offline
    Registered User
    Join Date
    06-24-2011
    Location
    North East England
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Countif? Sumproduct?

    Hi XLent, thank you for the formula! Would it be possible to post an example not using the _Data references? My example above is just a snippet of my full sheet (can't share full sheet due to data protection etc). I can then transfer the formula into my full sheet, as currently I am unsure where the _Data refers to within your example.
    Reply With QuoteRegister To Reply
  5. 01-10-2019, 08:02 AM #5
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: Countif? Sumproduct?

    _Data is a Dynamic Named Range -- refer to the Name Manager in Formula section on Ribbon

    you should modify the formula (range references) in this Name to mimic your real life dataset... in essence it is used to dynamically expand / contract relative to date limiter, in turn to avoid repetition in the subsequent Frequency calcs.
    Reply With QuoteRegister To Reply
  6. 01-11-2019, 12:24 AM #6
    FDibbins
    FDibbins is offline
    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
    53,048

    Re: Countif? Sumproduct?

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sorting multiple entries with several batches based on lowest balance.
    By Istid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2019, 05:54 PM
  2. Number clusters
    By shabazali1 in forum Excel General
    Replies: 9
    Last Post: 08-06-2018, 09:01 AM
  3. Only copying a single of multiple entries and counting qty of entries in a table
    By MML3329 in forum Excel General
    Replies: 1
    Last Post: 04-05-2016, 11:20 AM
  4. [SOLVED] Counting the number of entries in a column (but only once for consecutive entries)
    By 11416498 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-30-2014, 09:36 AM
  5. identifying random clumping (clusters) in 2D or 3D
    By pauliehagan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2012, 08:50 AM
  6. Weighing Point Clusters
    By zealot in forum Excel General
    Replies: 5
    Last Post: 12-25-2010, 04:18 PM
  7. Highlighting when stock is low if there are several entries (batches) of same product
    By alba in forum Excel General
    Replies: 10
    Last Post: 08-26-2010, 04:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1