Explanation
In this example, the goal is to count “z” or “c” values in the named range data , but only when the column header is “A” or “B”. The formula used to perform this calculation is based on the SUMPRODUCT function :
=SUMPRODUCT(ISNUMBER(MATCH(headers,{"A","B"},0))*ISNUMBER(MATCH(data,{"z","c"},0)))
Working from the inside out, note that SUMPRODUCT contains a single argument , which is composed of this expression:
ISNUMBER(MATCH(headers,{"A","B"},0))*ISNUMBER(MATCH(data,{"z","c"},0))
This expression is formed from two parts, each representing a logical test. The left part tests column headers, and the right tests values. The two parts are joined with multiplication (*) because the overall logic is AND, and multiplication corresponds to AND in Boolean algebra .
On the left , the MATCH function is used with the ISNUMBER function to match target columns:
ISNUMBER(MATCH(headers,{"A","B"},0)) // match "A" or "B"
Inside MATCH, notice the arguments are “reversed” to maintain the existing data structure: the header values are used for the lookup_value argument, and the array argument is provided as an array constant that contains the values we are looking for, “A” and “B”. The result from MATCH is an array composed of #N/A errors or numbers. The numbers indicate matched positions:
{1,2,#N/A,1,2,#N/A}
There are 6 items in this array because we are testing 6 columns. The numbers represent matched columns and errors represent columns that do not match. This array is returned and handed off to the ISNUMBER function :
ISNUMBER({1,2,#N/A,1,2,#N/A}) // convert to TRUE or FALSE
which returns an array like this:
{TRUE,TRUE,FALSE,TRUE,TRUE,FALSE}
Note the TRUE values correspond to columns that are either “A” or “B”. This completes the column matching logic.
On the right side of the expression, we have similar logic to test the values themselves:
ISNUMBER(MATCH(data,{"z","c"},0))
The MATCH function is again used to check for two values “z” or “c” with the same reversed argument approach. Because the named range data contains 60 values, the result from MATCH is an array with 60 values:
{2,#N/A,2,#N/A,1,1;#N/A,#N/A,2,2,1,2;2,2,#N/A,#N/A,#N/A,#N/A;#N/A,#N/A,1,2,2,2;#N/A,2,#N/A,2,#N/A,1;2,#N/A,2,2,2,2;2,2,#N/A,2,#N/A,2;#N/A,2,#N/A,#N/A,2,#N/A;1,#N/A,1,1,#N/A,1;2,1,2,#N/A,#N/A,2}
The ISNUMBER function again translates this array into TRUE and FALSE values:
{TRUE,FALSE,TRUE,FALSE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,TRUE,FALSE,TRUE;TRUE,FALSE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,TRUE,FALSE,FALSE,TRUE,FALSE;TRUE,FALSE,TRUE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE,TRUE}
Now the original expression above (inside SUMPRODUCT) can be written like this:
{TRUE,TRUE,FALSE,TRUE,TRUE,FALSE}*{TRUE,FALSE,TRUE,FALSE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,TRUE,FALSE,TRUE;TRUE,FALSE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,TRUE,FALSE,FALSE,TRUE,FALSE;TRUE,FALSE,TRUE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE,TRUE}
Note the multiplication operator is still there, just after the first array. In Excel any math operation will automatically convert TRUE and FALSE values to their numeric equivalents, 1 and 0. This means you can think of the expression like this:
{1,1,0,1,1,0}*{1,0,1,0,1,1;0,0,1,1,1,1;1,1,0,0,0,0;0,0,1,1,1,1;0,1,0,1,0,1;1,0,1,1,1,1;1,1,0,1,0,1;0,1,0,0,1,0;1,0,1,1,0,1;1,1,1,0,0,1}
After the expression is evaluated, we have a single array like this:
{1,0,0,0,1,0;0,0,0,1,1,0;1,1,0,0,0,0;0,0,0,1,1,0;0,1,0,1,0,0;1,0,0,1,1,0;1,1,0,1,0,0;0,1,0,0,1,0;1,0,0,1,0,0;1,1,0,0,0,0}
This array is delivered to the SUMPRODUCT function as the array1 argument. Then, with only one array to process, SUMPRODUCT sums the items in the array and returns a final result: 22.
Note: although SUMPRODUCT can handle multiple arrays as separate arguments, you will see many formulas that place all logic into a single argument. Doing so takes advantage of the fact that Excel automatically coerces TRUE and FALSE values to 1s and 0s during any math operation. When the logic is separated into separate arrays, an additional step must be taken to convert to 1s and 0s. For more details, see Why SUMPRODUCT?
Contains logic
In the example shown above testing logic is “equal to”. The columns must be equal to “A” or “B” and the values must be equal to “z” or “c”. But sometimes you need to test with “contains” logic. For example, test for values that contain “z” or contain “c”.
One consequence of reversing the arguments inside the MATCH function is that wildcards can’t be used with the lookup values, because these values appear as the array argument. If you need to test values using contains logic, you can switch to another approach based on ISNUMBER with the SEARCH function . For example, to match values that contain “x” or “c”, you can use an expression like this:
=ISNUMBER(SEARCH("z",data))+ISNUMBER(SEARCH("c",data))
Note we are joining each test with the addition operator (+) because in Boolean algebra addition corresponds to OR logic . The final formula would then look like this:
=SUMPRODUCT(ISNUMBER(MATCH(headers,{"A","B"},0))*(ISNUMBER(SEARCH("z",data))+ISNUMBER(SEARCH("c",data))))
Note an additional set of parentheses () have been added to control order of operations .
Explanation
In this example, the goal is to count the number of names in the range B5:B16 (Invited) that are missing from the range D5:D12 (Attended). This problem can be solved with the COUNTIF function or the MATCH function, as explained below. Both approaches work well. The advantage of the MATCH approach is that it will work with arrays or ranges . The COUNTIF function is limited to ranges only, like other functions in this group .
COUNTIF solution
The COUNTIF function counts cells that meet a single condition, which is referred to as “criteria”. The generic syntax for COUNTIF looks like this:
=COUNTIF(range,criteria)
For example, to count the cells in A1:A10 that are equal to “apple”, you could use COUNTIF like this:
=COUNTIF(A1:A10,"apple")
In this example, we are doing something interesting. We are giving COUNTIF more than one value to count, supplied in the range B5:B16, and we are asking COUNTIF to count these values in the range D5:D12:
=COUNTIF(D5:D12,B5:B16)
Literally, this formula means “count the values in B5:B16 that appear in D5:D12”. Because we are giving COUNTIF a range that contains 12 values, COUNTIF returns 12 results in an array like this:
{1;1;0;1;0;1;0;1;1;0;1;1}
The 1s in this array signify names in B5:B16 that appear in D5:D12. The 0s indicate names in B5:B16 that don’t appear in D5:D12. If the goal was to count matching values , we could sum the result from COUNTIF with the SUM function like this:
=SUM(COUNTIF(D5:D12,B5:B16)) // returns 8
The result would be 8 since there are 8 names in B5:B16 that appear in D5:D12. However, in this problem, the goal is to count missing values , so we need to “reverse” the result from COUNTIF before we sum. In other words, we need to convert the 1s to 0s and the 0s to 1s. We can do that by first comparing the result from COUNTIF to zero:
COUNTIF(D5:D12,B5:B16)=0
This will result in an array with 12 TRUE and FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
Notice that in this array, the FALSE values correspond to 1s in the previous array, and the TRUE values correspond to 0s. If we try to add these values up with the SUM function, the result will be zero, because SUM is programmed to ignore the logical values TRUE and FALSE. Before we sum, we need to convert the TRUE and FALSE values to 1s and 0s again. We do that with a double negative (–) operation:
--(COUNTIF(D5:D12,B5:B16)=0) // returns {0;0;1;0;1;0;1;0;0;1;0;0}
The result is a numeric array:
{0;0;1;0;1;0;1;0;0;1;0;0}
In this array, 0s represent names in B5:B16 that appear in D5:D12, and 1s represent missing names. This array is returned directly to the SUM function, and SUM returns 4 as a final result:
=SUM({0;0;1;0;1;0;1;0;0;1;0;0}) // returns 4
Note: In the current version of Excel, the SUM function works without special handling. In Legacy Excel , this is an array formula and must be entered with control + shift + enter. To avoid this requirement, you can replace SUM with SUMPRODUCT , and control + shift + enter is not required.
MATCH solution
Another way to solve this problem in a more literal way is to use the MATCH function to match names. The MATCH function returns the position of a value in a range. For example, if the range A1:A3 contains “orange”, “apple”, and “pear”, then the MATCH function will return 2 if we look for “apple”, because “apple” appears in the 2nd cell:
=MATCH("apple",A1:A3,0) // returns 2
If we look for a value that does not exist in the range, MATCH will return an #N/A error:
=MATCH("banana",A1:A3,0) // returns #N/A
We can use the behavior above to solve the problem in this example with a formula like this:
=SUM(--ISNA(MATCH(B5:B16,D5:D12,0)))
Working from the inside out, MATCH is configured to match the values in B5:B16 against the values in D5:D12:
MATCH(B5:B16,D5:D12,0)
Note that the value for match_type is zero (0) to specify an exact match. Like the COUNTIF formula above, we are asking MATCH to look for the values in B5:B16 (Invited) in the values in D5:D12 (attended). Because we are looking for 12 values, MATCH will return an array with 12 results like this:
{5;6;#N/A;1;#N/A;3;#N/A;7;8;#N/A;2;4}
In this array, numbers represent the position of a name that was found, and #N/A errors represent names that were not found. This array is returned directly to the ISNA function :
ISNA({5;6;#N/A;1;#N/A;3;#N/A;7;8;#N/A;2;4})
ISNA returns TRUE for #N/A errors and FALSE for anything else, so the result from ISNA looks like this:
{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
As with the COUNTIF formula, we want to count these results, but we first need to convert the TRUE and FALSE values to 1s and 0s. We do this with a double negative (–) operation:
=SUM(--{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE})
=SUM({0;0;1;0;1;0;1;0;0;1;0;0})
=4
The final result is 4 since there are 4 names in B5:B16 that do not appear in D5:D12.
Note: One advantage of the MATCH function is that it will work with data in arrays or ranges, while the COUNTIF function is limited to ranges only .
