by Crispo Mwangi | Mar 8, 2023 | ADVANCED EXCEL
SUMIF Data Ignoring Hidden Rows in Excel is not as simple as other types of summation using criteria. This is because the SUMIF function does not ignore hidden rows. The solution comes from using a combination of SUBTOTAL & OFFSET functions to return only visible...
by Crispo Mwangi | Feb 1, 2023 | ADVANCED EXCEL
If you do not have Office365, the easiest option you have for splitting text is FILTERXML How do you split and sum below multiple sales? The easiest way to solve this is by using the FILTERXML function FILTERXML function returns specific data from XML text using the...
by Crispo Mwangi | Jan 22, 2023 | ADVANCED EXCEL
Surendra Reddy on Linked posed a challenge on how to Clean & Split Dirty Data as shown below There are many ways of doing this but I find it easy to use below Power Query M code let Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],...
by Crispo Mwangi | Dec 2, 2022 | ADVANCED EXCEL
Here are 4 ways to Initialize the First and/or Second Name in Excel This article is inspired Bhavya Gupta Youtube Video. He elegantly solves the issue using LAMBDA function but I will try to solve solve it using normal TEXT functions. Using REPLACE & FIND Function...
by Crispo Mwangi | Sep 16, 2022 | ADVANCED EXCEL
Most lookup functions by default return either the 1st or the last match item. How do you return all matching multiple results in a lookup? How do you return all the customers who bought the product below? USING VLOOKUP TO RETURN MULTIPLE RESULTS...
by Crispo Mwangi | Aug 26, 2022 | ADVANCED EXCEL, HR
On Linkedin, after sharing my article on 7 Ways to Calculate Income tax, I got a question on how to calculate income tax in Pakistan. Below are their tax brackets USING SUMPRODUCT Step 1: Summarise the above brackets to a table like below Step 2: Use the below formula...
Recent Comments