count new entries from dates

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,653
Office Version
365
Platform
Windows
Unfortunately I have no idea how to do that.
Hopefully one of the formula folk, will step in & help.
 

Some videos you may like

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
Joined
Jun 29, 2014
Messages
5,146
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:

<b></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 /><col /><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><th>O</th><th>P</th><th>Q</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">63403</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">75705</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">55287</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">89542</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">69636</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">70352</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">88676</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">75367</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">70850</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">77030</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet506</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIF(<font color="Green">M3:M16,M3:M16</font>)=1</font>)*(<font color="Red">N3:N16=P3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,653
Office Version
365
Platform
Windows
@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
Joined
Jun 29, 2014
Messages
5,146
Thanks Fluff, so I misunderstood the question.

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

<b></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 /><col /><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><th>O</th><th>P</th><th>Q</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">63403</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">75705</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">55287</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">89542</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">69636</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">70352</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">88676</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">75367</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">70850</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">77030</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet506</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIF(<font color="Green">M3:M16,M3:M16</font>)=1</font>)*(<font color="Red">N3:N16<>P3-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

EDIT: think I may still be confused...:confused:
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,653
Office Version
365
Platform
Windows

ADVERTISEMENT

So simple when you know how :)
 

RodrigoFinguer

Board Regular
Joined
Jun 13, 2017
Messages
73
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...: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:

CellABCDEFGH
1Serial N.22/01/201923/01/201924/01/201925/01/201926/01/2019New Values (day 25/01)New Values (day 26/01)
21017841111
3102756111
4243576111
5875233
6542113111
769764511111
82265441111

<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
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

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:

<b></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 /><col /><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><th>O</th><th>P</th><th>Q</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">63403</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">75705</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">55287</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">89542</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">69636</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">70352</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">88676</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">75367</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">70850</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">77030</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet506</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">COUNTIFS(<font color="Green">M3:M16,M3:M16,N3:N16,P3-1</font>)=0</font>)*(<font color="Red">(<font color="Green">N3:N16=P3</font>)+(<font color="Green">N3:N16=P3-1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

RodrigoFinguer

Board Regular
Joined
Jun 13, 2017
Messages
73
Worksheet Formulas
CellFormula
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></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 /><col /><col /><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><th>O</th><th>P</th><th>Q</th><th>R</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">63403</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">75705</td><td style="text-align: right;;">1/22/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">55287</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">89542</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">69636</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">70352</td><td style="text-align: right;;">1/23/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">70859</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">84732</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">88676</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">75367</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">70850</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">77030</td><td style="text-align: right;;">1/24/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;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)">Sheet506</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">COUNTIFS(<font color="Green">M3:M16,M3:M16,N3:N16,P3-1</font>)=0</font>)*(<font color="Red">(<font color="Green">N3:N16=P3</font>)+(<font color="Green">N3:N16=P3-1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">COUNTIFS(<font color="Green">M3:M16,M3:M16,N3:N16,P3</font>)=0</font>)*(<font color="Red">(<font color="Green">N3:N16=P3</font>)+(<font color="Green">N3:N16=P3-1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

RodrigoFinguer

Board Regular
Joined
Jun 13, 2017
Messages
73
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)))

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top