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 →

In this article, we will create a macro to check duplication and remove duplicate values from two consecutive columns. Raw data consists of target details, which includes Agent name, Target model name and Desired model name. In … 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))}

In this article, we will use find method of Range object to highlight the cell which contains the value similar to search words. Raw data for this example consists of company name, employee id and employee name. We have raw … 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 →