SUMIFS Combined with DATE Functions

Welcome to Excel Jet Consult. In this tutorial, we will learn how to combine SUMIFS with DATE functions to return Qtr 1 Total Sales. Let’s get started In cell H1, execute the formula: =SUMIFS(Total,Order_Date,”>=”&DATE(2015,1,1),Order_Date,”<=”&DATE(2015,3,31)) We can see from the formula above that we wrapped the DATE formula inside SUMIFS To verify, we will filter the data set to show Order […]

Continue reading

XLOOKUP Function – New Elegant Lookup Function

Yesterday witnessed the beginning of a new dawn in world of spreadsheet occasioned by the arrival of an elegant, sophisticated and user-friendly Excel function named XLOOKUP. The new XLOOKUP function provides a strong alternative to the VLOOKUP function which has been around since 1985 when Excel 1 for Macintosh was released. Traditionally, the Excel VLOOKUP, among other limitations, is unable […]

Continue reading

Power Query M Functions: Extracting using Text.Start & Text.PositionOf

Welcome to Excel Jet Consult. In this tutorial, we will learn how to Text.Start and Text.PositionOf Power Query M Functions which are LEFT & FIND functions in Excel Text.Start returns the first count characters of text as a text value Text.Start Syntax                              =Text.Start(text as nullable text, count as number) Text.PositionOf returns the position of the specified occurrence of the text […]

Continue reading

Microsoft Excel Training @ Federal Ministry of Petroleum Resources, Abuja, Nigeria (4th Session)

Microsoft Excel Training workshop for Director of Human Resources Management and Senior Staffs at Federal Ministry of Petroleum Resources, Abuja, Nigeria (4th Session) Date: August 24th, 2019 Time: 1 am – 6:00 pm Topics Covered How to create Table and working with Table Understanding how to use named ranges Using List Data Validation How to use Text-to-column How to use […]

Continue reading

Microsoft Excel Training @ Federal Ministry of Petroleum Resources, Abuja, Nigeria (3rd Session)

Microsoft Excel Training workshop for Director of Human Resources Management and Senior Staffs at Federal Ministry of Petroleum Resources, Abuja, Nigeria (3rd Session) Date: August 24th, 2019 Time: 10 am – 12:00 pm Topics Covered How to Calculate Retirement Date By Years in Service & Date of Employment Create Pivot Table Working with Pivot Tables Field List Tabular Pivot Table […]

Continue reading

Microsoft Excel Training @ Federal Ministry of Petroleum Resources, Abuja, Nigeria (1st Session)

Microsoft Excel Training workshop for Director of Human Resources Management and Senior Staffs at Federal Ministry of Petroleum Resources, Abuja, Nigeria (1st Session) Date: August 23rd, 2019 Time: 10 am – 12:30 pm Topics Covered Introduction to Excel General Excel Formatting Freezing Panes and Splitting windows Using Arithmetic Operators in formulas Using SUM, AUTOSUM, AVERAGE, MAX, MIN Functions How to […]

Continue reading