This is a cruel Excel challenge (I’ve used it!). Why? Because there is a brilliantly simple solution to summing cells that overlap two ranges. Continue reading
Sum Cells in Overlapping Ranges
Author Archives → Kevin Lehrbass
Subtotal helper column
This formula =COUNTIFS($A$7:$A$309,$AK4,$B$7:$B$309,1) considers all rows within the given range when counting. What if we want to exclude filtered and/or hidden rows? Continue reading
How many unique List 1 names found in List 2?
Imagine two lists of names. We have three questions: (1)how many unique names in List 1? (2)how many unique names in List 2? (3)how many unique names from List 1 are found in List 2? Continue reading
Power Query solution: partial match count with a condition
In a previous post we saw how formulas can solve a partial match with conditions. Now I will solve it using Power Query because my friend Kunle challenged me to do so 🙂 Continue reading
3 Newly Added Charts in Excel 2019 You Must Learn to Create
This Guest Post is contributed by Aprajita Sharma from ExcelChamps. Learn about 3 new charts available in Excel 2019 (or with a Office 365 subscription). Continue reading
Partial Match Count with a Condition
Once again I discovered a formula that I had written down in a notebook. What is it doing? Is there an easier way to solve this? Continue reading
Sorted Data Validation List
This post from ExcelFORO explains how to use VBA to create a sorted data validation list. It’s a great solution! Are there other ways to solve this? Continue reading
Weighted Average
I’ll show you the weighted average formula, how it calculates, and I’ll prove that the answer is correct! Continue reading
Countifs confirms Vlookup
So many times vlookup (or index & match) saves the day and gets your answer! ….wait…are you sure it’s correct??? Read this post to see why you should use countifs to confirm your vlookup. Continue reading
CAGR in Microsoft Excel
Recently I was asked “How can you prove that the CAGR answer is correct?“. In this post I’ll show you how to verify the CAGR result. I’ll also review CAGR basics and pitfalls. Continue reading