Excel Tips and Tricks
Count values with Excel intersect operator
Excel intersect operation is handled by the space character (" "). You can intersect multiple rages by space in range supporting formulas. This article shows how to COUNT intersecting ranges with COUNT function and Excel intersect operator. Syntax...
How to lookup across two tables with VLOOKUP and IF functions
How to lookup across two tables by a condition is a combination of the VLOOKUP and the IF functions. The IF function handles which table and VLOOKUP handles what to search for. Syntax =VLOOKUP(lookup value, IF(condition, first table if condition is TRUE,...
How to count by month
To count by month has nothing different than How to COUNT values between two dates. However, this article shows you a more dynamic and specialized way with EOMONTH function that you don't need to guess how many days in a month to count month. If you only...
How to calculate monthly averages
This article shows you how to calculate monthly averages from a data table containing dates and values using AVERAGEIFS and EOMONTH functions. If you only want to calculate the number of days in a month, please refer to the article: How to find number of...
How to sum by month
To sum by month has nothing different than How to SUM values between two dates using SUMIFS formula. However, this article shows you a more dynamic and specialized approach combining EOMONTH and SUMIFS functions that you don't need to guess how many days...
How to find number of days in a month
This article shows you how to find number of days in a month to make your month-driven calculations dynamic. DAY and EOMONTH functions help us for this tip. Syntax =DAY(EOMONTH(Date, 0)) Steps Start with =DAY( Continue with EOMONTH( Select or type the...
How to make case sensitive match
If your data is case sensitive, you may have issues finding correct matches with lookup functions. This How to make case sensitive match article shows you how to combine MATCH and EXACT functions to achieve case sensitive matching. Syntax { =MATCH(TRUE,...
How to check if a value exists in a list
The logical tests and conditional checks have important role in Excel models. Potential errors can be detected and handled by using the right logical tests. If you know How to check if a value exists in a list, you can use this logical statement to detect...
How to find smallest value in array using a criteria
This article shows how to find smallest value in array using SMALL and IF functions in an array formula. Syntax {=SMALL(IF(criteria range = criteria, data range reference), nth)} Steps Start with =SMALL( function Continue with IF( Type the criteria...