sumproduct+countif+sumif

[kelvin_9]

greeting to all,

i am not very good at excel formula, with my data(A1:C19) below

i have to extract the date and time to date only(E2:E19), and then remove duplicate(F2:F3), to calculate the number of order SN(G2:G3)

however, i was wondering, if this 3 formula can be worked(J2:K6) at same time
ps: lower right corner is my expected answer

Mrh Online - Order with SOH (Prompt) Production - Pick Up - Limited Store - Hourly2023-12-29-20-01-01 (002).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Order Sn	Updated At	Location Translations.Title		Updated At	Updated At	Order Sn		Location Translations.Title	12/29/2023	12/28/2023
2	231220121812472908	12/29/2023	111		12/29/2023	12/29/2023	6		111
3	231220121812472908	12/29/2023	111		12/29/2023	12/28/2023	5		222
4	231220121812472908	12/29/2023	111		12/29/2023				333
5	231220121812472908	12/29/2023	111		12/29/2023				444
6	231221120210753305	12/29/2023	111		12/29/2023				555
7	231221120416786413	12/29/2023	222		12/29/2023
8	231221120444923279	12/29/2023	333		12/29/2023
9	231221120618872345	12/29/2023	444		12/29/2023
10	231221121739646978	12/29/2023	444		12/29/2023
11	231221122743920439	12/28/2023	111		12/28/2023
12	231221122743920439	12/28/2023	111		12/28/2023
13	231221122743920439	12/28/2023	111		12/28/2023
14	231221123648304322	12/28/2023	222		12/28/2023				Location Translations.Title	12/29/2023	12/28/2023
15	231221125907305575	12/28/2023	333		12/28/2023				111	2	1
16	231221132938506643	12/28/2023	444		12/28/2023				222	1	1
17	231221132938506643	12/28/2023	444		12/28/2023				333	1	1
18	231221132938506643	12/28/2023	444		12/28/2023				444	2	1
19	231221133430881381	12/28/2023	555		12/28/2023				555	0	1
Sheet1

Cell Formulas
Range	Formula
J1	J1	=F2
K1	K1	=F3
G2	G2	=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))
G3	G3	=SUMPRODUCT((A11:A19<>"")/COUNTIF(A11:A19,A11:A19&""))
E2:E19	E2	=INT(B2)

thank you very much for your answering

 

[kelvin_9]

The alternative using formulas is more involved, as you'll need to create the unique row and column headings, and then that table layout can be populated with a counting formula...I've adopted the version offered by @Phuoc. The rows and column heading formulas are entered in the first cell and then dragged across or down until blanks are returned, indicating all unique values are accounted for. Then the counting formula is copied throughout the table. I still prefer Power Query for this, but it's good to have options.

MrExcel_20231231.xlsx
	M	N	O	P
1	Location Translations.Title	12/29/2023	12/28/2023
2	111	2	1
3	222	1	1
4	333	1	1
5	444	2	1
6	555	0	1
7
Sheet4

Cell Formulas
Range	Formula
N1:P1	N1	=IFERROR(INDEX(INT($B$2:$B$19),MATCH(0,INDEX(COUNTIF($M$1:M1,INT($B$2:$B$19)),0),0)),"")
N2:O6	N2	=COUNT(1/(ROW($A$2:$A$19)-1=MATCH($A$2:$A$19&N$1&$M2,$A$2:$A$19&INT($B$2:$B$19)&$C$2:$C$19,0)))
M2:M7	M2	=IFERROR(INDEX($C$2:$C$19,MATCH(0,INDEX(COUNTIF($M$1:M1,$C$2:$C$19),0),0)),"")

thank you very much for your reply, KRice

formula at N1 and M2 is really save my life

thank you very much

 

[KRice]

Here is another idea for the data table formula:

MrExcel_20231231.xlsx
	M	N	O
1	Location Translations.Title	12/29/2023	12/28/2023
2	111	2	1
3	222	1	1
4	333	1	1
5	444	2	1
6	555	0	1
Sheet4

Cell Formulas
Range	Formula
N1:O1	N1	=IFERROR(INDEX(INT($B$2:$B$19),MATCH(0,INDEX(COUNTIF($M$1:M1,INT($B$2:$B$19)),0),0)),"")
M2:M6	M2	=IFERROR(INDEX($C$2:$C$19,MATCH(0,INDEX(COUNTIF($M$1:M1,$C$2:$C$19),0),0)),"")
N2:O6	N2	=SUM(IF(FREQUENCY(IF((INT($B$2:$B$19)=N$1)*($C$2:$C$19=$M2),$A$2:$A$19,""),IF((INT($B$2:$B$19)=N$1)*($C$2:$C$19=$M2),$A$2:$A$19,""))>0,1))