count new entries from dates

R

RodrigoFinguer

Board Regular
Joined
Jun 13, 2017
Messages
75
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.
 
Sort by date Sort by votes
Unfortunately I have no idea how to do that.
Hopefully one of the formula folk, will step in & help.
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
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
Cell Formulas
RangeFormula
Q3=SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16=P3))
 
Upvote 0
@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:
Upvote 0
Thanks Fluff, so I misunderstood the question.

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


Book1
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
Cell Formulas
RangeFormula
Q3=SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16<>P3-1))


EDIT: think I may still be confused...:confused:
 
Last edited:
Upvote 0
jtakw said:
Thanks Fluff, so I misunderstood the question.

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q3[/TH]
[TD="align: left"]=SUMPRODUCT(--(COUNTIF(M3:M16,M3:M16)=1)*(N3:N16<>P3-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



EDIT: think I may still be confused...:confused:
Click to expand...

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:

[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD="align: center"]Cell[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Serial N.[/TD]
[TD]22/01/2019[/TD]
[TD]23/01/2019[/TD]
[TD]24/01/2019[/TD]
[TD]25/01/2019[/TD]
[TD]26/01/2019[/TD]
[TD]New Values (day 25/01)[/TD]
[TD]New Values (day 26/01)[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]101784[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]102756[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]243576[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]875233[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]542113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]697645[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]226544[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Formula G2 (or any of this column)[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Formula H2 (or any of this column)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]=if(and(E2=1;D2="");1;0)[/TD]
[TD]=if(and(F2=1;E2="");1;0)[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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
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
Cell Formulas
RangeFormula
Q3=SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))
 
Upvote 0
jtakw said:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q3[/TH]
[TD="align: left"]=SUMPRODUCT(--(COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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!!!
 
Upvote 0
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.
Click to expand...

2nd, for your latest request, R3 formula:


Book1
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
Cell Formulas
RangeFormula
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:
Upvote 0
jtakw said:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q3[/TH]
[TD="align: left"]=SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3-1)=0)*((N3:N16=P3)+(N3:N16=P3-1)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R3[/TH]
[TD="align: left"]=SUMPRODUCT((COUNTIFS(M3:M16,M3:M16,N3:N16,P3)=0)*((N3:N16=P3)+(N3:N16=P3-1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
248
Nippyzap
N
T
  • Question Question
VBA to populate cells on one worksheet with cells from another worksheet based on date help!!!
Replies
2
Views
361
Taekwon
T
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
190
Lux Aeterna
L
S
  • Question Question
Look up todays date and special copy paste value for multiple sheets
Replies
4
Views
170
OaklandJim
O
C
  • Question Question
Adding New Data to Table with Inconsistent Entries Using VBA
Replies
0
Views
189
Curious George_1
C

Forum statistics

Threads
1,223,992
Messages
6,175,825
Members
452,672
Latest member
missbanana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top