Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To sum the top n values in a range matching criteria, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), range represents a range of cells that are compared to criteria , values represents numeric values from which...Read more
If you need to subtotal numbers by color, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: = SUMIF ( $B$4:$B$11 , F5 , $D$4:$D$11 ) How this formula works The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using:...Read more
To sum based on multiple criteria using OR logic, you can use the SUMIFS function with an array constant. In the example shown, the formula in H6 is: = SUM ( SUMIFS ( E4:E11 , D4:D11 , { "complete" , "pending" } )) How this formula works By default, the SUMIFS function only...Read more
To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. In the example shown, the formula in F6 is: = SUMPRODUCT ( -- ( ISNA ( MATCH ( lista , listb , 0 )))) > 0 How the formula works Normally, the MATCH...Read more
To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions. How the formula works In the example, B6 is the hard-coded start date and the formula in B7 is: = DATE ( YEAR ( B6 ), MONTH...Read more
Videos
Pivot tables are awesome tools for grouping data, and they do a lot of grouping automatically. But pivot tables also let you group data into your own custom buckets, which is great for...
In this video, we look at a step by step approach to randomly assign people to groups with teams using formulas. Along the way, we use three functions (RAND, RANK, and CEILING), helper...
In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
Combining functions is a core skill you *must* have in order to move beyond the basics in Excel. In this video, we show how to split, manipulate, and reassemble values using multiple...
In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested...
Blog
The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.
Excel contains over 450 functions. Where should you start? This guide is a walkthrough of over 100 important functions in Excel. Click function names for details and linked examples.
Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula. What?
Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.
Feedback
Thank you very much. The function is working fantastically. It saved me at least 3 full working days. - Rajesh
LOVE your site... wow... you have helped me quickly do a lot of things that would have taken me forever to figure out on my own. And I love that your search feature actually knows what I'm looking for... lol -Morgan
I have been using Excel for years and love it. Just want to tell you your site is great, and thank you for all your hard work! -Maury
We offer free resources and paid training.