count without duplicate

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
greeting to all

i was wondering is there any formula that i can quickly return how many order SN total in daily without duplicate calculation?
example: 11 order on 12/29/2023, 18 order on 12/23/2023

Book1
ACE
1Order SnCreated AtUpdated At
223122012181247290812/20/2023 12:18:11 PM12/29/2023
323122012181247290812/20/2023 12:18:11 PM12/29/2023
423122012181247290812/20/2023 12:18:11 PM12/29/2023
523122012181247290812/20/2023 12:18:11 PM12/29/2023
623122113293850664312/21/2023 1:29:38 PM12/29/2023
723122113293850664312/21/2023 1:29:38 PM12/29/2023
823122113293850664312/21/2023 1:29:38 PM12/29/2023
923122113343088138112/21/2023 1:34:30 PM12/29/2023
1023122112364830432212/21/2023 12:36:48 PM12/29/2023
1123122112041678641312/21/2023 12:04:16 PM12/29/2023
1223122112173964697812/21/2023 12:17:39 PM12/29/2023
1323122112061887234512/21/2023 12:06:18 PM12/29/2023
1423122112044492327912/21/2023 12:04:44 PM12/29/2023
1523122112021075330512/21/2023 12:02:10 PM12/29/2023
1623122112590730557512/21/2023 12:59:07 PM12/29/2023
1723122112274392043912/21/2023 12:27:43 PM12/29/2023
1823122112274392043912/21/2023 12:27:43 PM12/29/2023
1923122112274392043912/21/2023 12:27:43 PM12/29/2023
2023122323425474686912/23/2023 11:42:54 PM12/23/2023
2123122323374261449312/23/2023 11:37:42 PM12/23/2023
2223122322261068046912/23/2023 10:26:10 PM12/23/2023
2323122320291110686012/23/2023 8:29:11 PM12/23/2023
2423122320291110686012/23/2023 8:29:11 PM12/23/2023
2523122318363278373212/23/2023 6:36:32 PM12/23/2023
2623122318363278373212/23/2023 6:36:32 PM12/23/2023
2723122318363278373212/23/2023 6:36:32 PM12/23/2023
2823122318021073513312/23/2023 6:02:10 PM12/23/2023
2923122317580230451312/23/2023 5:58:02 PM12/23/2023
3023122317175182098312/23/2023 5:17:51 PM12/23/2023
3123122317175182098312/23/2023 5:17:51 PM12/23/2023
3223122315272869865012/23/2023 3:27:28 PM12/23/2023
3323122314123488995912/23/2023 2:12:34 PM12/23/2023
3423122313492547787412/23/2023 1:49:25 PM12/23/2023
3523122312424390431712/23/2023 12:42:43 PM12/23/2023
3623122312052580813212/23/2023 12:05:25 PM12/23/2023
3723122310485254666512/23/2023 10:48:52 AM12/23/2023
3823122303244757073412/23/2023 3:24:47 AM12/23/2023
3923122300550963460912/23/2023 12:55:09 AM12/23/2023
4023122300521884820112/23/2023 12:52:18 AM12/23/2023
4123122300521884820112/23/2023 12:52:18 AM12/23/2023
4223122300500986120112/23/2023 12:50:09 AM12/23/2023
4323122223450375330812/22/2023 11:45:03 PM12/22/2023
4423122223232060761612/22/2023 11:23:20 PM12/22/2023
4523122223232060761612/22/2023 11:23:20 PM12/22/2023
4623122222551643142712/22/2023 10:55:16 PM12/22/2023
4723122222250472779512/22/2023 10:25:04 PM12/22/2023
4823122222194066971112/22/2023 10:19:40 PM12/22/2023
4923122220163645864612/22/2023 8:16:36 PM12/22/2023
5023122220144079721212/22/2023 8:14:40 PM12/22/2023
5123122220121838867412/22/2023 8:12:18 PM12/22/2023
5223122220112228353712/22/2023 8:11:22 PM12/22/2023
5323122220112228353712/22/2023 8:11:22 PM12/22/2023
5423122220091578892412/22/2023 8:09:15 PM12/22/2023
5523122220091578892412/22/2023 8:09:15 PM12/22/2023
5623122219422227593312/22/2023 7:42:22 PM12/22/2023
5723122219341160318812/22/2023 7:34:11 PM12/22/2023
5823122219341160318812/22/2023 7:34:11 PM12/22/2023
5923122219301766232112/22/2023 7:30:17 PM12/22/2023
6023122219242440067312/22/2023 7:24:24 PM12/22/2023
6123122219242440067312/22/2023 7:24:24 PM12/22/2023
6223122219242440067312/22/2023 7:24:24 PM12/22/2023
6323122219011953773112/22/2023 7:01:19 PM12/22/2023
6423122219011953773112/22/2023 7:01:19 PM12/22/2023
6523122219011953773112/22/2023 7:01:19 PM12/22/2023
6623122218535888480212/22/2023 6:53:58 PM12/22/2023
6723122218470316436712/22/2023 6:47:03 PM12/22/2023
6823122218314396781612/22/2023 6:31:43 PM12/22/2023
6923122218191293013712/22/2023 6:19:12 PM12/22/2023
7023122218171973171912/22/2023 6:17:19 PM12/22/2023
7123122218094523186112/22/2023 6:09:45 PM12/22/2023
7223122218094523186112/22/2023 6:09:45 PM12/22/2023
7323122218030366178112/22/2023 6:03:03 PM12/22/2023
7423122218030366178112/22/2023 6:03:03 PM12/22/2023
7523122217552089584912/22/2023 5:55:20 PM12/22/2023
7623122217524646693712/22/2023 5:52:46 PM12/22/2023
7723122217524646693712/22/2023 5:52:46 PM12/22/2023
Sheet2
 
Sort by date Sort by votes
It is Ctrl + Shift + Enter.

Did you have a look at the woorkbook I uploaded and shared in the previous post?

If your can upload your file to a cloud storage (Google drive, dropbox, etc) I'll have a look.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
felixstraube said:
It is Ctrl + Shift + Enter.

Did you have a look at the woorkbook I uploaded and shared in the previous post?

If your can upload your file to a cloud storage (Google drive, dropbox, etc) I'll have a look.
Click to expand...
thank you very much for your reply, felixstraube

sorry for the typo mistake

yes i did
i copied my data range to your workbook / use your formula on my workbook
not good

my workbook

thank you very much
 
Upvote 0
Empty cells produced the error.
Here is the corrected formula. This should work:

Excel Formula: 
=SUM((FREQUENCY(IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5), IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5)) > 0) * 1) - 1
 
Upvote 0
felixstraube said:
Empty cells produced the error.
Here is the corrected formula. This should work:

Excel Formula: 
=SUM((FREQUENCY(IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5), IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5)) > 0) * 1) - 1
Click to expand...
thank you very much for your reply, felixstraube

i copied and paste to my workbook, still has empty cells, (pic 1)
even i copied to notepad and delete first, remain unchanged (pic 2)

is it caused the data range too large?

last but not least, in your workbook and your page, your formula with empty cell is funtioning well

9.jpg

thank you very much
 
Upvote 0
Formula is working fine. It returns 0 because you don't have any date 12/29/2023 in column C.
in fact you just have these 4 dates:

12/20/2023
12/21/2023
12/22/2023
12/23/2023

So you are getting this:

CountWithoutDuplicates - 2.xlsx
ABCDEFGH
1Order SnCreated AtUpdated At
223122012002019411312/20/2023 12:00:20 PM12/29/2023
323122012002019411312/20/2023 12:00:20 PM12/29/2023
423122012002019411312/20/2023 12:00:20 PM12/29/2023Updated At
523122012004199114012/20/2023 12:00:41 PM12/20/20232023-12-290
623122012004388862712/20/2023 12:00:43 PM12/20/20232023-12-201991
723122012004388862712/20/2023 12:00:43 PM12/20/20232023-12-23250
823122012005822121912/20/2023 12:00:58 PM12/29/20232023-12-212520
923122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-280
1023122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-270
1123122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-250
1223122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-223163
1323122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-240
1423122012020737078312/20/2023 12:02:07 PM12/20/20231900-01-000
1523122012020737078312/20/2023 12:02:07 PM12/20/2023 
1623122012020737078312/20/2023 12:02:07 PM12/20/2023 
Sheet1
Cell Formulas
RangeFormula
H5:H14H5=SUM((FREQUENCY(IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5), IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5)) > 0) * 1) - 1
G5:G16G5=IFERROR(INDEX(INT($E$2:$E$20000),MATCH(0,INDEX(COUNTIF($G$4:G4,INT($E$2:$E$20000)),0),0)),"")
 
Upvote 0
felixstraube said:
Formula is working fine. It returns 0 because you don't have any date 12/29/2023 in column C.
in fact you just have these 4 dates:

12/20/2023
12/21/2023
12/22/2023
12/23/2023

So you are getting this:

CountWithoutDuplicates - 2.xlsx
ABCDEFGH
1Order SnCreated AtUpdated At
223122012002019411312/20/2023 12:00:20 PM12/29/2023
323122012002019411312/20/2023 12:00:20 PM12/29/2023
423122012002019411312/20/2023 12:00:20 PM12/29/2023Updated At
523122012004199114012/20/2023 12:00:41 PM12/20/20232023-12-290
623122012004388862712/20/2023 12:00:43 PM12/20/20232023-12-201991
723122012004388862712/20/2023 12:00:43 PM12/20/20232023-12-23250
823122012005822121912/20/2023 12:00:58 PM12/29/20232023-12-212520
923122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-280
1023122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-270
1123122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-250
1223122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-223163
1323122012014130737812/20/2023 12:01:41 PM12/29/20232023-12-240
1423122012020737078312/20/2023 12:02:07 PM12/20/20231900-01-000
1523122012020737078312/20/2023 12:02:07 PM12/20/2023 
1623122012020737078312/20/2023 12:02:07 PM12/20/2023 
Sheet1
Cell Formulas
RangeFormula
H5:H14H5=SUM((FREQUENCY(IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5), IFERROR(MATCH($A$2:$A$20000, $A$2:$A$20000, 0),0) * (INT($C$2:$C$20000) = $G5)) > 0) * 1) - 1
G5:G16G5=IFERROR(INDEX(INT($E$2:$E$20000),MATCH(0,INDEX(COUNTIF($G$4:G4,INT($E$2:$E$20000)),0),0)),"")
Click to expand...
thank you very much for your reply, felixstraube

i will try it when i get back home tonight
if i want to add one more criteria to match B10:J10 and column AY
what should i need to add in this formula then

thank you very much

final.xlsx
ABCDEFGHIJKLMAY
1
2aaa
3bbb
4ccc
5ddd
6eee
7fff
8ggg
9111128136138141154171175176hhh
10aaabbbcccdddeeefffggghhhiiiiii
11Friday, 29 December 2023222
12Wednesday, 20 December 20231020
13Saturday, 30 December 2023330
14Tuesday, 2 January 2024453
15Sunday, 31 December 2023253
16Monday, 1 January 2024159
17Wednesday, 3 January 2024407
18Saturday, 23 December 2023208
19Thursday, 21 December 20232197
20Wednesday, 27 December 20232
21Monday, 25 December 20232
22Friday, 22 December 20232669
23Sunday, 24 December 20232
24Saturday, 0 January 19000
Sheet1
Cell Formulas
RangeFormula
A11:A24A11=IFERROR(INDEX(INT($AE$2:$AE$20000),MATCH(0,INDEX(COUNTIF($A$10:A10,INT($AE$2:$AE$20000)),0),0)),"")
B11:B24B11=SUM((FREQUENCY(IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11),IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11))>0)*1)-1
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
how about this?:

CountWithoutDuplicates.xlsx
ABCDEFGHIJAAABACADAEAXAY
1Order SnCreated AtUpdated At
223122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMaaa
323122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMbbb
423122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMccc
523122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMddd
623122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMeee
723122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMfff
823122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMggg
923122113343088100012/21/2023 01:34:30 PM12/29/2023 12:35:14 PMhhh
10aaabbbcccdddeeefffggghhhiii23122112364830400012/21/2023 12:36:48 PM12/29/2023 11:24:38 AMiii
1112/29/202341111111123122112041678600012/21/2023 12:04:16 PM12/29/2023 11:12:44 AMaaa
1212/20/202300000000023122112173964600012/21/2023 12:17:39 PM12/29/2023 11:11:27 AMaaa
1312/30/202300000000023122112061887200012/21/2023 12:06:18 PM12/29/2023 10:23:29 AMaaa
1401/02/202400000000023122112044492300012/21/2023 12:04:44 PM12/29/2023 10:20:39 AM
1523122112021075300012/21/2023 12:02:10 PM12/29/2023 10:18:22 AM
1623122112590730500012/21/2023 12:59:07 PM12/29/2023 10:15:14 AM
1723122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1823122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1923122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
2023122323425474600012/23/2023 11:42:54 PM12/23/2023 11:43:37 PM
2123122323374261400012/23/2023 11:37:42 PM12/23/2023 11:37:57 PM
2223122322261068000012/23/2023 10:26:10 PM12/23/2023 10:26:38 PM
2323122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2423122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2523122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2623122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2723122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2823122318021073500012/23/2023 06:02:10 PM12/23/2023 06:03:23 PM
2923122317580230400012/23/2023 05:58:02 PM12/23/2023 05:59:22 PM
3023122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3123122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3223122315272869800012/23/2023 03:27:28 PM12/23/2023 03:27:55 PM
Hoja1
Cell Formulas
RangeFormula
B11:J14B11=SUM((FREQUENCY(IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000),IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000))>0)*1)-1
 
Upvote 0
how about this?:
felixstraube said:
CountWithoutDuplicates.xlsx
ABCDEFGHIJAAABACADAEAXAY
1Order SnCreated AtUpdated At
223122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMaaa
323122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMbbb
423122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMccc
523122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMddd
623122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMeee
723122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMfff
823122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMggg
923122113343088100012/21/2023 01:34:30 PM12/29/2023 12:35:14 PMhhh
10aaabbbcccdddeeefffggghhhiii23122112364830400012/21/2023 12:36:48 PM12/29/2023 11:24:38 AMiii
1112/29/202341111111123122112041678600012/21/2023 12:04:16 PM12/29/2023 11:12:44 AMaaa
1212/20/202300000000023122112173964600012/21/2023 12:17:39 PM12/29/2023 11:11:27 AMaaa
1312/30/202300000000023122112061887200012/21/2023 12:06:18 PM12/29/2023 10:23:29 AMaaa
1401/02/202400000000023122112044492300012/21/2023 12:04:44 PM12/29/2023 10:20:39 AM
1523122112021075300012/21/2023 12:02:10 PM12/29/2023 10:18:22 AM
1623122112590730500012/21/2023 12:59:07 PM12/29/2023 10:15:14 AM
1723122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1823122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1923122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
2023122323425474600012/23/2023 11:42:54 PM12/23/2023 11:43:37 PM
2123122323374261400012/23/2023 11:37:42 PM12/23/2023 11:37:57 PM
2223122322261068000012/23/2023 10:26:10 PM12/23/2023 10:26:38 PM
2323122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2423122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2523122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2623122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2723122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2823122318021073500012/23/2023 06:02:10 PM12/23/2023 06:03:23 PM
2923122317580230400012/23/2023 05:58:02 PM12/23/2023 05:59:22 PM
3023122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3123122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3223122315272869800012/23/2023 03:27:28 PM12/23/2023 03:27:55 PM
Hoja1
Cell Formulas
RangeFormula
B11:J14B11=SUM((FREQUENCY(IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000),IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000))>0)*1)-1
Click to expand...
thank you very much for your reply, felixstraube

i am curious why do i work with the formula on my work book but not in the macro with run time error 1004?

VBA Code: 
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("C51").Select
    Selection.FormulaArray = _
        "=SUM((FREQUENCY(IFERROR(MATCH(data!R2C1:R20000C1,data!R2C1:R20000C1,0),0)*(INT(data!R2C5:R20000C5)=RC1)*(R49C=data!R2C25:R20000C25),IFERROR(MATCH(data!R2C1:R20000C1,data!R2C1:R20000C1,0),0)*(INT(data!R2C5:R20000C5)=RC1)*(R49C=data!R2C25:R20000C25))>0)*1)-1"
End Sub

thank you very much
 
Upvote 0
FormulaArray only works with formulas less than 255 characters. That looks like it might be a bit longer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Networkdays formula issue
Replies
3
Views
258
Joe4
Joe4
K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
78
kumara_faith
K
S
  • Solved
Count of active employees between dates.
Replies
3
Views
628
Fluff
Fluff
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
69
Fuji
F
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
265
Cubist
Cubist

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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