SUMPRODUCT Archives - Xelplus - Leila Gharani https://www.xelplus.com/tag/sumproduct/ Bestselling Excel and Power BI Courses Fri, 24 Mar 2023 06:29:37 +0000 en-US hourly 1 https://www.xelplus.com/wp-content/uploads/2022/10/cropped-site-icon-32x32.png SUMPRODUCT Archives - Xelplus - Leila Gharani https://www.xelplus.com/tag/sumproduct/ 32 32 How to Use Excel SUMIFS on Multiple Criteria in a Single Column https://www.xelplus.com/sumifs-multiple-criteria-in-a-single-column/ Thu, 26 Aug 2021 06:49:00 +0000 https://www.xelplus.com/?p=9844 Learn how to sum values based on multiple criteria in a single columns. You probably know that you can use SUMIFS to sum values based on criteria from multiple columns. But there is also an easy way you can sum values if your criteria is in the same column.

The post How to Use Excel SUMIFS on Multiple Criteria in a Single Column appeared first on Xelplus - Leila Gharani.

]]> Use this Excel Trick to Only Count Cells with Text (Exclude Numbers and Blanks) https://www.xelplus.com/excel-formula-hack-count-text/ Thu, 05 Mar 2020 10:00:00 +0000 https://www.xelplus.com/?p=11441 Check out this Excel formula hack to count text cells only. This mysterious syntax excludes blank cells, numbers and also cells with formulas that result in an empty string.

The post Use this Excel Trick to Only Count Cells with Text (Exclude Numbers and Blanks) appeared first on Xelplus - Leila Gharani.

]]> Excel RANK without Skipping Numbers (Top 3 Report with Duplicates) https://www.xelplus.com/excel-rank-without-skipping-numbers/ Sat, 16 Mar 2019 14:54:00 +0000 https://www.xelplus.com/?p=12800 Learn about Excel's Rank and Rank.EQ functions. How can you rank values without skipping numbers in between and how to create a top 3 report with duplicates.

The post Excel RANK without Skipping Numbers (Top 3 Report with Duplicates) appeared first on Xelplus - Leila Gharani.

]]> Excel Complex Lookup: Find Header https://www.xelplus.com/complex-lookup-index-sumproduct/ Tue, 05 Sep 2017 09:52:00 +0000 https://www.xelplus.com/?p=11968 In this tutorial we tackle a complex lookup problem - how to return the header based on criteria in a matrix. I offer my solution using INDEX and SUMPRODUCT. We also cover alternative approaches proposed by the Excel community, including an interesting TEXTJOIN solution that is able to handle multiple match results.

The post Excel Complex Lookup: Find Header appeared first on Xelplus - Leila Gharani.

]]> Excel SumIFS – Sum Alternate Columns Based on Criteria and on Header https://www.xelplus.com/excel-sumifs-sum-alternate-columns-based-on-header/ Fri, 21 Apr 2017 05:08:00 +0000 https://www.xelplus.com/?p=12554 In this video tutorial, you will learn how to sum different columns based on user selection with the SUMIFS or SUMPRODUCT function. It's a useful technique for interactive dashboards.

The post Excel SumIFS – Sum Alternate Columns Based on Criteria and on Header appeared first on Xelplus - Leila Gharani.

]]> Lookup Within Boundaries https://www.xelplus.com/lookup-within-boundaries/ Sun, 12 Feb 2017 10:20:00 +0000 https://www.xelplus.com/?p=12635 This tutorial takes you through three powerful Excel formulas that lookup values in ranges that include lower bound and upper bound values. They are SUMIFS, SUMPRODUCT and INDEX/MATCH. Find the one that works best for your scenario.

The post Lookup Within Boundaries appeared first on Xelplus - Leila Gharani.

]]> Excel Arrays: Count Unique Items with MMULT Function (Part 2/3) https://www.xelplus.com/excel-arrays-count-unique-items-with-mmult-function/ Mon, 23 Jan 2017 15:45:00 +0000 https://www.xelplus.com/?p=12705 Learn how to get the unique count across rows using an array formula based on the MMULT and SUMPRODUCT functions.

The post Excel Arrays: Count Unique Items with MMULT Function (Part 2/3) appeared first on Xelplus - Leila Gharani.

]]> 8 Formulas to get Unique Count Across Rows in Excel (Part 1/3) https://www.xelplus.com/excel-array-formula-unique-count-rows-excel-part1/ Thu, 19 Jan 2017 08:11:00 +0000 https://www.xelplus.com/?p=12721 Learn how to get the unique count across rows using an array formula based on the SUMPRODUCT and FREQUENCY functions.

The post 8 Formulas to get Unique Count Across Rows in Excel (Part 1/3) appeared first on Xelplus - Leila Gharani.

]]> Excel SUMPRODUCT – Calculate Year To Date https://www.xelplus.com/sumproduct-year-to-date/ Wed, 30 Nov 2016 10:27:00 +0000 https://www.xelplus.com/?p=13085 Learn how to write dynamic YTD formulas in Excel using SUMPRODUCT, OFFSET & SUM functions, even when your data set goes over a few years, with each month occurring more than once.

The post Excel SUMPRODUCT – Calculate Year To Date appeared first on Xelplus - Leila Gharani.

]]> Master Excel’s SUMPRODUCT Formula https://www.xelplus.com/excel-sumproduct-formula-easy-explanation/ Wed, 30 Nov 2016 10:25:00 +0000 https://www.xelplus.com/?p=13101 Master one of the most useful functions in Excel - SUMPRODUCT. This tutorial explains how it works in easy steps. You'll understand why you have to use a multiplication operator or a double negative, how to handle 'and' & 'or' conditions, and what to watch out for. You can test your knowledge with the attached exercise book.

The post Master Excel’s SUMPRODUCT Formula appeared first on Xelplus - Leila Gharani.

]]>