count new entries from dates
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: count new entries from dates

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: count new entries from dates

    Unfortunately I have no idea how to do that.
    Hopefully one of the formula folk, will step in & help.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: count new entries from dates

    Hi,

    Fluff's formula in Post # 4 gives the Correct result of 4 for your sample and description in Post # 10.

    Don't know why you're getting 6:

    MNOPQ
    2Serial No.Date
    3708591/22/20191/24/20194
    4847321/22/2019
    5634031/22/2019
    6757051/22/2019
    7552871/23/2019
    8895421/23/2019
    9696361/23/2019
    10703521/23/2019
    11708591/24/2019
    12847321/24/2019
    13886761/24/2019
    14753671/24/2019
    15708501/24/2019
    16770301/24/2019

    Sheet506



    Worksheet Formulas
    CellFormula
    Q3=SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16=P3))


  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: count new entries from dates

    @jtakw
    My formula is ignoring a Serial No. if it has occurred any where in the past, whereas the OP only wants to ignore it, if it occurred the day before.
    so with this data

    Excel 2013/2016
    MN
    2Serial No.Date
    3708591/22/2019
    4847321/22/2019
    5634031/22/2019
    6757051/22/2019
    7708501/23/2019
    8895421/23/2019
    9696361/23/2019
    10703521/23/2019
    11708591/24/2019
    12847321/24/2019
    13886761/24/2019
    14753671/24/2019
    15708501/24/2019
    16770301/24/2019

    Offer Received







    Even though the green cells are duplicate they should be counted as the duplicate was not on the 23rd. Whereas the red cells should not be counted.
    Last edited by Fluff; Jan 28th, 2019 at 02:18 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: count new entries from dates

    Thanks Fluff, so I misunderstood the question.

    But if that's the case, would this fix it?

    MNOPQ
    2Serial No.Date
    3708591/22/20191/24/20196
    4847321/22/2019
    5634031/22/2019
    6757051/22/2019
    7552871/23/2019
    8895421/23/2019
    9696361/23/2019
    10703521/23/2019
    11708591/24/2019
    12847321/24/2019
    13886761/24/2019
    14753671/24/2019
    15708501/24/2019
    16770301/24/2019

    Sheet506



    Worksheet Formulas
    CellFormula
    Q3=SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16<>P3-1))



    EDIT: think I may still be confused...
    Last edited by jtakw; Jan 28th, 2019 at 02:45 PM.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: count new entries from dates

    So simple when you know how
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular
    Join Date
    Jun 2017
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count new entries from dates

    Quote Originally Posted by jtakw View Post
    Thanks Fluff, so I misunderstood the question.


    Worksheet Formulas
    Cell Formula
    Q3 =SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16<>P3-1))



    EDIT: think I may still be confused...
    It didn't worked... when i used big data the value returned was ALL new values EXCEPT the last day using your formula, so, since I have values from 2nd january it counts ALL new values.

    This is how i am doing right now using pivot table:

    Cell A B C D E F G H
    1 Serial N. 22/01/2019 23/01/2019 24/01/2019 25/01/2019 26/01/2019 New Values (day 25/01) New Values (day 26/01)
    2 101784 1 1 1 1
    3 102756 1 1 1
    4 243576 1 1 1
    5 875233
    6 542113 1 1 1
    7 697645 1 1 1 1 1
    8 226544 1 1 1 1


    I use pivot table, using counting of serial numbers by date, and at the end i just do as follows:

    Formula G2 (or any of this column)
    Formula H2 (or any of this column)
    =if(and(E2=1;D2="");1;0) =if(and(F2=1;E2="");1;0)


    This way i can do a graph per day showing how many values i had new, per day, just summing columns G and H.

    Obs: sorry for my bad table, i don't know how to customize. Sorry also for my english, I am not fluent.

  7. #17
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: count new entries from dates

    Ok, I think I figured out what you want and how to get it with an updated formula.

    But looking at your most recent post, I don't know if you've changed the formatting of your data, the following works based on the your setup in Post # 3:

    MNOPQ
    2Serial No.Date
    3708591/22/20191/24/20196
    4847321/22/2019
    5634031/22/2019
    6757051/22/2019
    7552871/23/2019
    8895421/23/2019
    9696361/23/2019
    10703521/23/2019
    11708591/24/2019
    12847321/24/2019
    13886761/24/2019
    14753671/24/2019
    15708501/24/2019
    16770301/24/2019

    Sheet506



    Worksheet Formulas
    CellFormula
    Q3=SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))


  8. #18
    Board Regular
    Join Date
    Jun 2017
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count new entries from dates

    Quote Originally Posted by jtakw View Post

    Worksheet Formulas
    Cell Formula
    Q3 =SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))
    Oh my god that worked! You guys are genius!
    I don't know if i have to open a new thread... but I would like also, the same logic, count how many serial numbers I have as output, in other words, how many serial numbers I had yesterday that I don't have today anymore. Anyway, I will try to modify your formula to get it.

    Thanks so much!!!

  9. #19
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: count new entries from dates

    You're welcome, glad it worked for you.

    2 things.
    1st, realized the double-unary ( -- ) is not needed, doesn't hurt, but not needed, Q3 formula.

    but I would like also, the same logic, count how many serial numbers I have as output, in other words, how many serial numbers I had yesterday that I don't have today anymore.
    2nd, for your latest request, R3 formula:

    MNOPQR
    2Serial No.Date
    3708591/22/20191/24/201964
    4847321/22/2019
    5634031/22/2019
    6757051/22/2019
    7552871/23/2019
    8895421/23/2019
    9696361/23/2019
    10703521/23/2019
    11708591/24/2019
    12847321/24/2019
    13886761/24/2019
    14753671/24/2019
    15708501/24/2019
    16770301/24/2019

    Sheet506



    Worksheet Formulas
    CellFormula
    Q3=SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))
    R3=SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3)=0)*((N3:N16=P3)+(N3:N16=P3-1)))

    Last edited by jtakw; Jan 31st, 2019 at 07:09 PM.

  10. #20
    Board Regular
    Join Date
    Jun 2017
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count new entries from dates

    Quote Originally Posted by jtakw View Post
    Worksheet Formulas
    Cell Formula
    Q3 =SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))
    R3 =SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3)=0)*((N3:N16=P3)+(N3:N16=P3-1)))
    It worked, although, how can I do the same formula selecting the date from Column "N", returning me 1 for the new values and 0 for not new values?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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