• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

SumProduct with PowerQuery|Power Query|Excel Forum|My Online Training Hub

You are here: Home / SumProduct with PowerQuery|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QuerySumProduct with PowerQuery
sp_PrintTopic sp_TopicIcon
SumProduct with PowerQuery
Avatar
Rajsha B
India
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 7, 2020
sp_UserOfflineSmall Offline
1
April 7, 2020 - 10:36 pm
sp_Permalink sp_Print

Hi All, I am new user to this forum. First of all thanks for launching such forum to enhance excel based knowledge.

I have a scenario with Power Query where i need to run the query with sumproduct. The formula which i used in excel is

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

This i want to implement with power query for the below data in column "E" (In "Formula" Header) . The sample data is below. 

Rep Code Date/Time In Date/Time Out Formula
11 21381 4/18/18 2:44 AM 4/18/18 6:49 AM FALSE
11 21381 4/18/18 7:16 AM 4/18/18 7:16 AM FALSE
11 21381 4/26/18 12:24 PM 4/26/18 3:24 PM FALSE
13 70105 4/20/17 4:52 AM 4/20/17 5:59 AM FALSE
13 97813 8/30/19 3:11 PM 8/30/19 3:29 PM FALSE
13 43100 12/18/19 5:22 PM 12/18/19 8:25 PM FALSE
14 52334 4/28/17 6:03 PM 4/28/17 7:26 PM FALSE
14 6018 5/10/17 6:28 PM 5/11/17 12:30 AM FALSE
14 81033 5/16/17 3:09 PM 5/16/17 7:26 PM FALSE
14 11259 5/16/17 10:39 PM 5/17/17 12:50 AM FALSE
14 10987 5/17/17 12:51 AM 5/17/17 1:27 AM FALSE
14 10951 5/17/17 1:28 AM 5/17/17 2:45 AM FALSE
14 1970 5/22/17 12:47 PM 5/22/17 2:05 PM FALSE
14 1970 5/22/17 2:20 PM 5/22/17 9:18 PM FALSE
14 71351 5/24/17 7:37 PM 5/24/17 9:04 PM FALSE
14 19254 5/26/17 1:29 AM 5/26/17 2:30 AM FALSE
14 65914 6/6/17 6:22 PM 6/6/17 8:45 PM FALSE
14 71351 6/6/17 8:46 PM 6/6/17 10:11 PM FALSE
14 10431 6/13/17 11:27 AM 6/13/17 1:17 PM TRUE
14 10951 6/13/17 11:27 AM 6/13/17 1:32 PM TRUE
14 71470 6/13/17 1:18 PM 6/13/17 1:50 PM TRUE
14 73693 6/17/17 3:03 PM 6/17/17 6:35 PM FALSE
14 71470 6/27/17 11:06 PM 6/28/17 1:05 AM FALSE
14 92797 6/28/17 1:07 AM 6/28/17 2:05 AM FALSE
14 77637 7/11/17 6:10 PM 7/11/17 8:01 PM TRUE
14 46236 7/11/17 6:10 PM 7/11/17 9:30 PM TRUE
14 77637 7/24/17 7:56 PM 7/24/17 10:59 PM FALSE
14 31439 7/25/17 10:53 AM 7/25/17 1:21 PM FALSE
14 34574 7/31/17 1:42 PM 7/31/17 5:13 PM FALSE
14 21452 8/7/17 9:43 AM 8/7/17 12:46 PM FALSE
14 21423 8/9/17 8:56 PM 8/9/17 9:25 PM FALSE
14 95377 8/11/17 7:14 PM 8/11/17 9:35 PM FALSE
14 21423 8/21/17 3:56 PM 8/21/17 5:22 PM FALSE
14 1202 9/18/17 9:22 AM 9/18/17 11:06 AM FALSE
14 32670 9/18/17 11:07 AM 9/18/17 11:21 AM FALSE
14 5363 10/2/17 7:31 AM 10/2/17 9:34 AM FALSE
14 32670 10/7/17 4:37 AM 10/7/17 6:25 AM FALSE
14 81711 10/11/17 1:24 PM 10/11/17 2:25 PM FALSE
14 81711 10/17/17 5:27 PM 10/17/17 9:35 PM FALSE
14 17040 11/2/17 9:55 AM 11/2/17 11:29 AM FALSE
14 84700 11/6/17 8:15 AM 11/6/17 12:16 PM FALSE
14 54158 11/20/17 3:00 PM 11/20/17 4:49 PM FALSE
14 1323 11/27/17 8:29 AM 11/27/17 12:29 PM FALSE

 

Thanks for the support you team provide. Thanks a lot.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
April 8, 2020 - 7:37 am
sp_Permalink sp_Print

Hello,

It seems to me that you could use IF statements by adding a conditional column.
https://www.myonlinetraininghu.....statements

Br,
Anders

Avatar
Rajsha B
India
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 7, 2020
sp_UserOfflineSmall Offline
3
April 8, 2020 - 2:54 pm
sp_Permalink sp_Print

Hi Anders,

Thanks for your quick reply. I tried with the above information provided in the link. But it is not working as i need. Can you please advise me the best way approach. 

Thanks 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
April 8, 2020 - 7:50 pm
sp_Permalink sp_Print

Hello,

If you can upload a sample file and describe in text what the logics are. What is it you want as end result?

Avatar
Rajsha B
India
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 7, 2020
sp_UserOfflineSmall Offline
5
April 8, 2020 - 10:22 pm
sp_Permalink sp_Print

Hi Sir, 

   I have uploaded sample date and also, i have mentioned the description/ what result that i need in the sample excel file itself. 

Thanks for you help

Rajsha

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
April 9, 2020 - 2:18 am
sp_Permalink sp_Print

Hello,

I am afraid I don't understand what you are looking for here. You write "Need to find the overlapping dates", what do you mean by overlapping? All the dates/times in column C are less than those in column D, except for row 3 where they are equal.

There are some dates/times in column C with same Rep# that has same date/time value, but they are not the same in column D. What is it that defines when overlapping is true or false?

Br,
Anders

Avatar
Rajsha B
India
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 7, 2020
sp_UserOfflineSmall Offline
7
April 10, 2020 - 3:20 pm
sp_Permalink sp_Print sp_EditHistory

Hi Anders,

Sorry for the late reply below is what i need to do in the sheet.

I have multiple columns with 2 dates columns. I need to count the rows that meets the 2 dates criteria.

objective: Identify obvious potential overlaps between the dates based on Order#. If one order# is started before the last one is completed, we should flag that as Yes. This overlapping should be done based on Rep#.

My challenge is getting the current row in Rep# as criteria. Please see the sample data for more reference. The formula that i use is listed below, Also i have attached excel ln the previous chain of the mail above. For better understanding i changed my sumproduct formula to countifs..

=IF((COUNTIFS($C$2:$C$40,"<="&D2,$D$2:$D$40,">="&C2,$A$2:$A$40,"="&A2))>1,"YES","NO") 

Thanks.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
April 11, 2020 - 6:14 pm
sp_Permalink sp_Print

Hello,

I have looked up on this topic and it seems it is a lot more difficult to get what you want using Power Query. Although it seems doable but it also seems you need to master the M formula language, which I don't.

Hopefully you will find a solution.

Br,
Anders

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
April 11, 2020 - 9:45 pm
sp_Permalink sp_Print sp_EditHistory

Hi Rajsha,

Add your table to power query, then add a new column with this formula:

= Table.AddColumn(#"Changed Type", "Count", (x)=> Table.RowCount(Table.SelectRows(#"Changed Type", each (_[#"Rep#"] = x[#"Rep#"]) and (_[#"Date/Time In"] >= x[#"Date/Time In"])and (_[#"Date/Time Out"] <= x[#"Date/Time Out"]) )))

Basically, you can get the most part of the syntax by filtering the table, after filtering you will get a step like this (make sure you filter DateTime In column with a date filter, After):

= Table.SelectRows(#"Filtered Rows", each [#"Date/Time In"] >= #datetime(2018, 4, 18, 2, 44, 0) and [#"Date/Time In"] <= #datetime(2018, 4, 26, 12, 24, 0))

Simply edit the formula in the formula bar, change the second time In to Time Out, and add the Rep criteria:

= Table.SelectRows(#"Filtered Rows", each [#"Rep#"]=2 and [#"Date/Time In"] >= #datetime(2018, 4, 18, 2, 44, 0) and [#"Date/Time Out"] <= #datetime(2018, 4, 26, 12, 24, 0))

The red parts you can edit in the formula bar, no need to enter into Advanced editor. After you make these edits, copy the final text of this edited formula, we will use it in a new column.

You can delete the steps you did to get the syntax right, and now you can add a new column with the formula you have created, Paste the formula you saved before, and click ok to finalize the process. Don't worry if it will return errors, we still have to make some adjustments in the formula bar, we have almost all we need in the formula.

each must be replaced with: (x)=>

[#"Rep#"] must be replaced with _[#"Rep#"] (just put an underscore before what is in there already)

[#"Date/Time In"] must be replaced with _[#"Date/Time In"] (just put an underscore before what is in there already)

[#"Date/Time Out"] must be replaced with _[#"Date/Time Out"] (just put an underscore before what is in there already)

2 must be replaced with x[#"Rep#"]

#datetime(2018, 4, 18, 2, 44, 0) must be replaced with x[#"Date/Time In"]

#datetime(2018, 4, 26, 12, 24, 0)) must be replaced with x[#"Date/Time Out"]

The new column will contain tables, each table will contain the rows matching the current row criterias, all you have to do is to wrap the formula into Table.CountRows, to count them.

 

Or, just add a new column with the formula provided at the beginning of this message, I tried to explain how you can do it yourself using the menu and formula bar only.

sp_AnswersTopicAnswer
Answers Post
Avatar
Rajsha B
India
Member
Members
Level 0
Forum Posts: 22
Member Since:
April 7, 2020
sp_UserOfflineSmall Offline
10
April 13, 2020 - 5:18 pm
sp_Permalink sp_Print

Hi Catalin Bombea, 

Thank you soo much.. This is what I needed, I'm just trying to understand the code what you did, but this is really fantastic. Hats off to you sir. Sorry for the trouble i gave. The explanation that you gave was very helpful. Kudos to you..SmileSmile

Thanks a lot

Rajsha.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard West, Clayton Watson
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27292

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.