# count new entries from dates

#### Fluff

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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### jtakw

##### Well-known Member
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:

#### Fluff

##### MrExcel MVP, Moderator
@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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Serial No.</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;background-color: #8DC182;;">70859</td><td style=";">1/22/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #8DC182;;">84732</td><td style=";">1/22/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">63403</td><td style=";">1/22/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">75705</td><td style=";">1/22/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #D96A75;;">70850</td><td style=";">1/23/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">89542</td><td style=";">1/23/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">69636</td><td style=";">1/23/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">70352</td><td style=";">1/23/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;background-color: #8DC182;;">70859</td><td style=";">1/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;background-color: #8DC182;;">84732</td><td style=";">1/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">88676</td><td style=";">1/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">75367</td><td style=";">1/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;background-color: #D96A75;;">70850</td><td style=";">1/24/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">77030</td><td style=";">1/24/2019</td></tr></tbody></table><p style="width:11.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Offer Received</p><br /><br />

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:

#### jtakw

##### Well-known Member
Thanks Fluff, so I misunderstood the question.

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

EDIT: think I may still be confused...

Last edited:

#### Fluff

##### MrExcel MVP, Moderator

So simple when you know how

#### RodrigoFinguer

##### Board Regular
Thanks Fluff, so I misunderstood the question.

Worksheet Formulas
CellFormula
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

##### Well-known Member

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:

#### RodrigoFinguer

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

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

##### Well-known Member
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:

Last edited:

#### RodrigoFinguer

##### Board Regular
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)))

</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?

### Forum statistics

1,106,186
Messages
5,509,691
Members
408,749
Latest member
Bhuvaneshvar kashyap

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...