Count sold and remaining

In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An "x" in the Sold column indicates the item has been sold. As is typical in Excel, there are several ways to solve this problem. The article below explains two approaches. 

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:

=COUNTA(B5:B16) // returns 12

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:

=COUNTA(C5:C16) // returns 7

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:

=COUNTA(B5:B16)-COUNTA(C5:C16) // returns 5

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:

=F5-F6 // use existing values

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:

=COUNTIF(B5:B16,"<>") // count not blank

To count sold items you can use either of these formulas:

=COUNTIF(C5:C16,"x") // count equal to "x" 
=COUNTIF(C5:C16,"<>") // count not blank

To count items not sold, you can use COUNTIF like this:

=COUNTIF(C5:C16,"") // count blank

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:

=SUMPRODUCT(--(B5:B11=C5:C11))

For more information about how this formula works, see this explanation.