Sumproduct with Power Query

[rajrajsha]

Hi All,

In excel it is very easy to write sumproduct, but how to do the same formula with Power Query.

Belwo is my formula which goes in excel is 

=SUMPRODUCT((C2<=$D$2:$D$84000)*(D2>=$C$2:$C$84000)*(A2=$A$2:$A$84000))>1

I need to do the same formula with Power Query. Can some one help me in this case to write the code in PQ. For bette understanding i linked my data image and sample data below.

Rep	Number	Time In	Time Out	Status
13	8039	3/10/17 8:47 AM	3/10/17 9:10 AM	FALSE
13	8025	3/10/17 1:34 PM	3/10/17 2:14 PM	FALSE
13	8126	3/22/17 6:00 AM	3/22/17 7:14 AM	FALSE
13	8217	4/14/17 6:33 AM	4/14/17 7:33 AM	FALSE
13	8217	4/20/17 4:52 AM	4/20/17 5:59 AM	FALSE
13	1241	8/30/19 3:11 PM	8/30/19 3:29 PM	FALSE
13	1280	12/18/19 5:22 PM	12/18/19 8:25 PM	FALSE
14	8255	4/28/17 6:03 PM	4/28/17 7:26 PM	FALSE
14	8310	5/10/17 6:28 PM	5/11/17 12:30 AM	FALSE
14	8338	5/16/17 3:09 PM	5/16/17 7:26 PM	FALSE
14	8341	5/16/17 10:39 PM	5/17/17 12:50 AM	FALSE
14	8341	5/17/17 12:51 AM	5/17/17 1:27 AM	FALSE
14	8341	5/17/17 1:28 AM	5/17/17 2:45 AM	FALSE
14	8360	5/22/17 12:47 PM	5/22/17 2:05 PM	FALSE
14	8360	5/22/17 2:20 PM	5/22/17 9:18 PM	FALSE
14	8367	5/24/17 7:37 PM	5/24/17 9:04 PM	FALSE
14	8371	5/26/17 1:29 AM	5/26/17 2:30 AM	FALSE
14	8416	6/6/17 6:22 PM	6/6/17 8:45 PM	FALSE
14	8367	6/6/17 8:46 PM	6/6/17 10:11 PM	FALSE
14	8441	6/13/17 11:27 AM	6/13/17 1:17 PM	TRUE
14	8341	6/13/17 11:27 AM	6/13/17 1:32 PM	TRUE
14	8447	6/13/17 1:18 PM	6/13/17 1:50 PM	TRUE
14	8477	6/17/17 3:03 PM	6/17/17 6:35 PM	FALSE
14	8447	6/27/17 11:06 PM	6/28/17 1:05 AM	FALSE
14	8509	6/28/17 1:07 AM	6/28/17 2:05 AM	FALSE
14	8557	7/11/17 6:10 PM	7/11/17 8:01 PM	TRUE
14	8554	7/11/17 6:10 PM	7/11/17 9:30 PM	TRUE
14	8557	7/24/17 7:56 PM	7/24/17 10:59 PM	FALSE
14	8613	7/25/17 10:53 AM	7/25/17 1:21 PM	FALSE
14	8633	7/31/17 1:42 PM	7/31/17 5:13 PM	FALSE
14	8662	8/7/17 9:43 AM	8/7/17 12:46 PM	FALSE
14	8662	8/9/17 8:56 PM	8/9/17 9:25 PM	FALSE
14	8679	8/11/17 7:14 PM	8/11/17 9:35 PM	FALSE
14	8662	8/21/17 3:56 PM	8/21/17 5:22 PM	FALSE
14	8820	9/18/17 9:22 AM	9/18/17 11:06 AM	FALSE
14	8813	9/18/17 11:07 AM	9/18/17 11:21 AM	FALSE
14	8870	10/2/17 7:31 AM	10/2/17 9:34 AM	FALSE

Thanks for your support in this. 

[v-frfei-msft · Accepted solution]

Hi @rajrajsha ,

Hope this case can help you.

https://www.myonlinetraininghub.com/excel-forum/power-query/countifs-or-sumproduct-in-power-query-or...

[rajrajsha]

Hi v-frfei-msft, Thanks for addressing my query. I will look into it and will let you know the status. Thanks you soo much for taking some time for suggesting the solutions. Im really happy to have such community and imso proud to have it.

Thanks 

Rajsha