#COUNTIFS #CountMultipleCriteria #COUNTIFSfunction For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course: https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning Website: https://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition My old blog: http://datascopic.net/blog-2-2
It’s easy to split columns in Power Query and split into rows. But here’s a situation where there’s data in 3 columns that will fly apart and get unmatched if we split one column without splitting the other 2 at the same time. One solution would be to split the source data into 3 pieces, […]
A question came in from someone who wanted to know how to merge rows of data into a single cell. Imagine you have a person’s name in one column and then details about the person on multiple rows; and then another name and details about that person; and so on … Now you’d like to […]
One of Excel’s newest functions: LET() Incredible! This function allows the Excel user to create complex formulas that are easier to create and easier to read. But they do require the user to think several steps ahead. In this video I show 3 examples and make 2 cases in favor of using LET: – Very […]
Thank to my friend Gráinne who showed me this in Power Query: Try … Otherwise In native Excel we have the IFERROR function for when we anticipate errors and know what we want to do with them. In this case, we want to add the Shipped and Inventory amounts. But, if someone has put a […]
This video is a little different than the rest. Someone needed help and I came up with 2 solutions–both shown in this video. What’s more important about this is the discussion about the hard solution vs. the concise solution. Sometimes the hard solution serves us better because we understand it. The concise solution may look […]
A friend contacted me and asked how Excel might help calculate points for what he described as a “buddy system” where he goes to the gym. Here’s the challenge: – Marvin and Drew are paired in this buddy system – If one attends a class, the team gets 1 point. – If both attend together, […]
COUNTA is an unsung hero. It’s not likely to show up on anyone’s Top 10 Excel functions or features. Yet, COUNTA does a lot! In this video I show multiple ways that COUNTA continues to be a favorite of mine–even with the glamorous features like XLOOKUP, Power Query, Dynamic Arrays, etc. You’ll see: – COUNTA […]
One way to get good at Excel is to find multiple ways to solve a problem. In this video I look at a recent problem where I pasted data into Excel from a website. The data ended up in a tall stack, and what I needed was buried in the mix. I immediately thought of […]