+ Reply to Thread
Results 1 to 12 of 12

Sum ifs - Based on Matching Date (exact date not range)

  1. #1
    Registered User
    Join Date
    06-02-2014
    Posts
    4

    Sum ifs - Based on Matching Date (exact date not range)

    I am trying to count the number of records If cell B (text) is unique and cell C (date) are on the same date.

    For example:
    (B) ------------------------- (C)
    Dollar General (ARC 20)-------2/20/2014
    Dollar General (ARC 20)-------2/20/2014
    Dollar General (ARC 20)-------2/26/2014
    Dollar General (ARC 20)-------2/20/2014
    Dollar General (ARC 20)-------2/20/2014
    Dollar General (ARC 20)-------3/31/2014
    DG 31-Pack ------------------2/20/2014

    Based on the above data, I should have a count of 4. So basically I want to count if B and C match.

    I have part of the formula working, but it is only doing the first part, if Column B matchs - =SUM(IF(FREQUENCY(MATCH(B2:B307,B2:B307,0),MATCH(B2:B1000,B2:B307,0))>0,1))

    I need something for if C also matches.

    Any ideas? Thanks in advance

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum ifs - Based on Matching Date (exact date not range)

    This gives a count of the value in column B and the date in column C combination that match in columns B and C. I suspect that there is more to it than just this.

    This will give a count of 4 in the example given.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Sum ifs - Based on Matching Date (exact date not range)

    I suspect so, too. What if there are two sets of pairs that match: A-B occurring twice and C-D occurring twice
    Glenn



  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Sum ifs - Based on Matching Date (exact date not range)

    I suspect so, too. What if there are two sets of pairs that match: A-B occurring twice and C-D occurring twice.

    Also, does your equation work if the first row does not contain the most commonly occurring pair of strings?

  5. #5
    Forum Contributor
    Join Date
    01-28-2008
    MS-Off Ver
    Excel 2016
    Posts
    149

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Can you concatenate A and B and count the number of unique occurrences that result?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum ifs - Based on Matching Date (exact date not range)

    The COUNTIFS matches the criteria used. In this case the value in column B is matched with the date in column C and that combination is counted in the range defined.

    If you enter this formula in column D and copy down, it will give a count for each unique combination as one is found.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-02-2014
    Posts
    4

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Based on the above, it was counting both items in column B:
    Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00
    Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00

    So the data shows two deals with the same same name and on the same date, so the count should be 1. But should also only show a total of 1, as I will later sum this amount to get the total.

    I am going to try concatenating the columns, and getting a unique count that way.


    Thanks!

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Make sure that the entries that you cite are "exactly" the same and not just look the same. A leading or trailing space will not be obvious but makes the entries different as far as Excel is concerned.

    Can you post the data? I would like to see why you got the result you did because you shouldn't have.

  9. #9
    Registered User
    Join Date
    06-02-2014
    Posts
    4

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Here you go
    Attached Files Attached Files
    Last edited by alyssakhan; 06-02-2014 at 06:29 PM.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum ifs - Based on Matching Date (exact date not range)

    The only thing that I can think of is that the formula wasn't correctly converted to be used with your data. I copied the formula from my previous message, pasted it into the worksheet, changed the cell ranges and it worked as it should...take a look at column F
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-02-2014
    Posts
    4

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Thanks for your help! The only thing it is still counting the total number of deals, so if I were to total the amount, it would be incorrect.


    Some background: I am trying to getting a count of all the transactions closed on the same day, if they have the same portfolio name and closed on the same day, it is considered 1 transaction (not the 6 individual records)

    Here are the results from the excel sheet, in red: what I want to see is 1, a total of 1. I just want the unique count.

    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00 6
    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
    Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
    Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00 2
    Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00

    Do you think concatenating both cells and getting a unique count would be best?

    Thanks again!
    Last edited by alyssakhan; 06-03-2014 at 01:48 PM.

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Sum ifs - Based on Matching Date (exact date not range)

    Change the formula in F1 to this and copy down. This will give 1 for each set of matches instead of the count of the matches.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Straight Line Formula based on exact date of purchase
    By cawinkel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-07-2012, 06:24 PM
  2. LOOKUP Having one Exact matching criteria and one between 2 date ranges.
    By Grahame Hamilton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2012, 06:28 AM
  3. [SOLVED] How to match the exact value from the date range?
    By PRADEEPB270 in forum Excel General
    Replies: 2
    Last Post: 12-13-2011, 11:52 AM
  4. Searching for exact term with date range
    By 97hills in forum Excel General
    Replies: 9
    Last Post: 02-06-2011, 10:00 AM
  5. copy date based on date -refer to date range
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2006, 08:35 AM

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