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

 

[KRice]

Is the Order Sn to be considered? You use it in your G2:G3 formulas, but your expected results results table seems to ignore the Order Sn. Are you familiar with Power Query? You can process the data source table and transform it into the desired output with a few steps. Note that my results don't look like your expected ones, so I might be misunderstanding something.

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Updated At", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Location Translations.Title", "Updated At"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US")[#"Updated At"]), "Updated At", "Count", List.Sum)
in
    #"Pivoted Column"

 

[Sam_D_Ben]

I am not sure if this work for you. Please check-

Book1
	I	J	K
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
Sheet1

Cell Formulas
Range	Formula
J1	J1	=F2
K1	K1	=F3
J2:K6	J2	=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$19,INT($B$2:$B$19)=INT(J$1*($C$2:$C$19=$I2))))), 0)

Thanks

 

[KRice]

@Sam_D_Ben, the OP’s profile indicates that Excel 2019 is being used, so FILTER and UNIQUE would not be available…however, perhaps the profile is incorrect. Importantly, you are taking a count of unique Order Sn’s for each combination of “Location Translations.Title” and date. That may answer the question I posed and explain the discrepancy between my result and the expected.

 

[kelvin_9]

Is the Order Sn to be considered? You use it in your G2:G3 formulas, but your expected results results table seems to ignore the Order Sn. Are you familiar with Power Query? You can process the data source table and transform it into the desired output with a few steps. Note that my results don't look like your expected ones, so I might be misunderstanding something.

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Updated At", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Location Translations.Title", "Updated At"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US")[#"Updated At"]), "Updated At", "Count", List.Sum)
in
    #"Pivoted Column"

thank you very much for your reply, Krice

yes, colmun A - order SN is considered
my goal is need to return how many order SN base on column B and column C without duplicate calculation
as i said i am not a excel expert and i dont know power query so i use two stupid method to do it
first, extract the date and remove duplicate
second, maybe a sumifs in J2:K6

thanks again for the guidance

 

[kelvin_9]

I am not sure if this work for you. Please check-

Book1
	I	J	K
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
Sheet1

Cell Formulas
Range	Formula
J1	J1	=F2
K1	K1	=F3
J2:K6	J2	=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$19,INT($B$2:$B$19)=INT(J$1*($C$2:$C$19=$I2))))), 0)

Thanks

thank you for your reply, Sam_D_Ben

i use v2016 at office and v2019 at home, both return "that function is invaild"

 

[Phuoc]

Or try this in J2:

=COUNT(1/(ROW($A$2:$A$19)-1=MATCH($A$2:$A$19&J$1&$I2,$A$2:$A$19&INT($B$2:$B$19)&$C$2:$C$19,0)))

Enter with Ctrl+Shift+Enter.

 

[KRice]

If you want to investigate using Power Query, copy the code below to your clipboard (click on the clipboard icon in the upper right as a shortcut). Then select any cell in your source table (perhaps cell B3) and then choose Data>Get Data>From Other Sources>From Table/Range. This will open up a small dialog box to convert your data range to an official Excel table. Confirm that the auto-detected range covers the correct range, and also confirm that your table has headings (in row 1 in this example). Power Query will automatically open and insert your table into it for processing. Then instead of using Power Query's Graphical User Interface to repeat the steps I've shown, you can take another shortcut: select View>Advanced Editor (which opens the PQ editor...this is where you can write code directly). Delete everything in the editor window and paste the code from your clipboard and confirm by clicking Done. You should see your processed/transformed results appear in PQ. Finally, from within PQ, click File>Close & Load and use the options to specify where you want the resulting table to be placed in your Excel workbook. I chose cell M1 of the current worksheet where the original source data are found, but you can specify other locations.

The advantage of this approach is that there are no formulas in your worksheet and you don't need to build the row and column headings or paste a counting formula through the results table...and it is dynamic. As your source table changes in size, the results can be refreshed by clicking on Data>Refresh All.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // convert date/time format to date only
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Updated At", type date}}),
    // remove duplicate rows, leaving only unique combinations of Order Sn, Location Translations.Title, and Date
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    // group de-duplicated table by Location Translations.Title and Date, and count aggregated results (which is the number of Order Sn within that subgrouping
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Location Translations.Title", "Updated At"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    // pivot the table to show dates across the columns, Location Translations.Title in rows, and the counts of Order Sn's as the table data
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Updated At", type text}}, "en-US")[#"Updated At"]), "Updated At", "Count", List.Sum),
    // get all column names as list
    allColumnNames = Table.ColumnNames(#"Pivoted Column"),
    // transform col name list into a list of lists, where every inner list contains col name and a function for replacing null value
    allTransformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
    // apply the transformations
    transformColumns = Table.TransformColumns(#"Pivoted Column", allTransformations)
in
    transformColumns

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
Sheet2

 

[KRice]

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)),"")

 

[kelvin_9]

Or try this in J2:

=COUNT(1/(ROW($A$2:$A$19)-1=MATCH($A$2:$A$19&J$1&$I2,$A$2:$A$19&INT($B$2:$B$19)&$C$2:$C$19,0)))

Enter with Ctrl+Shift+Enter.

thank you very much for your reply, Phuoc
this is really fantastic

however, i found my formula below dosent work as yours, and returns only "1"
and also, if my data is more than 1000, likely A2:A2000, it will be very very slow running even restart the workbook
=COUNT(1/(ROW($A$2:$A$100)-1=MATCH($A$2:$A$100&AF$1&$AE2,$A$2:$A$100&INT($E$2:$E$100)&$AE$2:$AE$10,0)))

thank you very much