Problem: Columns A:C contain product categories, customer names, and prices paid for various items. When using the following formula to sum the prices paid by Customer1 on category A products, #NUM! is returned: =SUMPRODUCT((A:A=”A”)*(B:B=”Customer1″),C:C) Solution: Use the SUMPRODUCT function as … Continue reading →

Problem: The following formula searches column A for each of the serial numbers listed in column C. It then analyses the corresponding number from column B: for numbers less than or equal to 40, 10 is added; and for numbers … Continue reading →

If there are any duplicate entries in column B that match column A, you can use this code to delete them. Sub DeleteMatches() Dim rRangeA As Range Dim rRangeB As Range Dim rCell As Range Set rRangeA = … Continue reading →

To retrieve the value of the first non blank cell in a list, we will use Index and Match functions. Why and how we use Index and Match functions together to retrieve the first nonblank value from a range? Index … Continue reading →

Problem: Counting the number of values in List1 (column A) that appear only once within the list. Solution: Use the SUM, IF, and COUNTIF functions as shown in the following Array formula: {=SUM(IF(COUNTIF(A2:A7,A2:A7&””)=1,1,0))}

I have used this so much I cannot do without it – it is a quick method to find all the ranges within a range than containing a value. The function is [Select Code] copy to clipboard Function Find_Range(Find_Item As … Continue reading →

Problem: Counting the total number of times each of the substrings listed in column B appears within any of the strings in column A. Solution: Use the SUMPRODUCT, LEN, and SUBSTITUTE functions as shown in the following formula: =SUMPRODUCT(LEN($A$2:$A$6)- LEN(SUBSTITUTE($A$2:$A$6,B2,” … Continue reading →

In this article, we are going to learn how to count rows of even numbers in two parallel ranges in Microsoft Excel. To solve this problem, we will use SUMPRODUCT & MOD function. SUMPRODUCT function is used to count the … Continue reading →

Problem: We want to create a sequence of numbers where each member of the sequence is the sum of the previous N number of values. Solution: Use the SUM, OFFSET, INDIRECT, ADDRESS, ROW, and COLUMN functions as shown in the … Continue reading →

Problem: Finding the number of players (column A) who scored over 3 points (column C) in the first quarter (column B). Solution: Use the SUMPRODUCT function as shown in the following formula: =SUMPRODUCT((B2:B17=F2)*(C2:C17>F3)) Screenshot // Counting the Number of Players … Continue reading →