The result is 5, since 7 out of 12 items have been sold. You can also use the COUNTIF function to solve this problem. Both approaches are described below.
COUNTA function
The COUNTA function counts non-blank cells in a range. Unlike the COUNT function, which only counts numeric values, COUNTA will count any value in a cell, including numbers and text. The first formula in F5 counts the total items available: The result is 12 since there are 12 values in the ID column. The second formula counts the number of items that have been sold: The result is 7 since there are 7 values in the Sold column. Note that COUNTA doesn’t care what value is in a cell. In the example shown, we are using an “x” to indicate sold items, but COUNTA would count “y” or “z” in the same way. The last formula counts the number of remaining items: The result is 5 since 12 minus 7 equals 5. In this example, last formula above is an all-in-one formula, to provide more detail. However, in this particular case, the best practice would be to write the last formula like this: In other words, we simply re-use existing results. This minimizes the number of calculations performed and reduces errors.
COUNTIF function
The COUNTIF function counts values in a range based on supplied criteria. With COUNTIF the problem can be solved a bit differently. To count total items, you can use COUNTIF like this: To count sold items you can use either of these formulas: To count items not sold, you can use COUNTIF like this:
Match test
If you need to make sure that the value in column C matches the value in column B, in the same row, you can use a formula based on the SUMPRODUCT function instead: For more information about how this formula works, see this explanation.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
