Exceljet

Quick, clean, and to the point

Count sold and remaining

Excel formula: Count sold and remaining
Generic formula 
=COUNTA(range1)-COUNTA(range2)
Summary 

To count how many items have been sold, and how many items remain, you can use the COUNTA function. In the example shown, the formula in F7 is:

=COUNTA(B5:B16)-COUNTA(C5:C16)

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.

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.