# Thread: count new entries from dates Thanks:  4 Post #5215832 (1)Post #5216697 (1)Post #5217997 (1)Post #5215806 (1) Likes:  1 Post #5214065 (1)

1. ## 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.

2. ## 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. ## 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

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.

4. ## 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...

5. ## Re: count new entries from dates

So simple when you know how

6. ## Re: count new entries from dates

Originally Posted by jtakw
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. ## 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. ## Re: count new entries from dates

Originally Posted by jtakw

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. ## 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)))

10. ## Re: count new entries from dates

Originally Posted by jtakw
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?