count new entries from dates

[RodrigoFinguer]

Hi guys, i really need this. I am thinking about more than a month but i can't get it!

I have values (serial numbers) in different dates, i will send a picture for better understanding.

I need to calculate the serial numbers which is new, the values that weren't yesterday in my sheet but it is today. I just know how to do it using dynamic table... there is a way to do it with formulas? Thank you so much!!

In my image, the green values are the same in both days and the blue ones are the new values.

 

[Fluff]

Unfortunately I have no idea how to do that.
Hopefully one of the formula folk, will step in & help.

 

[jtakw]

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:

Book1
	M	N	O	P	Q
2	Serial No.	Date
3	70859	1/22/2019		1/24/2019	4
4	84732	1/22/2019
5	63403	1/22/2019
6	75705	1/22/2019
7	55287	1/23/2019
8	89542	1/23/2019
9	69636	1/23/2019
10	70352	1/23/2019
11	70859	1/24/2019
12	84732	1/24/2019
13	88676	1/24/2019
14	75367	1/24/2019
15	70850	1/24/2019
16	77030	1/24/2019
Sheet506

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

 

[Fluff]

@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
	M	N
2	Serial No.	Date
3	70859	1/22/2019
4	84732	1/22/2019
5	63403	1/22/2019
6	75705	1/22/2019
7	70850	1/23/2019
8	89542	1/23/2019
9	69636	1/23/2019
10	70352	1/23/2019
11	70859	1/24/2019
12	84732	1/24/2019
13	88676	1/24/2019
14	75367	1/24/2019
15	70850	1/24/2019
16	77030	1/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.

 

[jtakw]

Thanks Fluff, so I misunderstood the question.

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

Book1
	M	N	O	P	Q
2	Serial No.	Date
3	70859	1/22/2019		1/24/2019	6
4	84732	1/22/2019
5	63403	1/22/2019
6	75705	1/22/2019
7	55287	1/23/2019
8	89542	1/23/2019
9	69636	1/23/2019
10	70352	1/23/2019
11	70859	1/24/2019
12	84732	1/24/2019
13	88676	1/24/2019
14	75367	1/24/2019
15	70850	1/24/2019
16	77030	1/24/2019
Sheet506

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

EDIT: think I may still be confused...

 

[Fluff]

So simple when you know how

 

[RodrigoFinguer]

Thanks Fluff, so I misunderstood the question.

​

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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) <tbody> </tbody>
=if(and(E2=1;D2="");1;0)	=if(and(F2=1;E2="");1;0)

<tbody>
</tbody>

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.

 

[jtakw]

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:

Book1
	M	N	O	P	Q
2	Serial No.	Date
3	70859	1/22/2019		1/24/2019	6
4	84732	1/22/2019
5	63403	1/22/2019
6	75705	1/22/2019
7	55287	1/23/2019
8	89542	1/23/2019
9	69636	1/23/2019
10	70352	1/23/2019
11	70859	1/24/2019
12	84732	1/24/2019
13	88676	1/24/2019
14	75367	1/24/2019
15	70850	1/24/2019
16	77030	1/24/2019
Sheet506

Cell Formulas
Range	Formula
Q3	=SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))

 

[RodrigoFinguer]

Worksheet Formulas Cell Formula Q3 =SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!!!

 

[jtakw]

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:

Book1
	M	N	O	P	Q	R
2	Serial No.	Date
3	70859	1/22/2019		1/24/2019	6	4
4	84732	1/22/2019
5	63403	1/22/2019
6	75705	1/22/2019
7	55287	1/23/2019
8	89542	1/23/2019
9	69636	1/23/2019
10	70352	1/23/2019
11	70859	1/24/2019
12	84732	1/24/2019
13	88676	1/24/2019
14	75367	1/24/2019
15	70850	1/24/2019
16	77030	1/24/2019
Sheet506

Cell Formulas
Range	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)))

 

[RodrigoFinguer]

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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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?