Quick, clean, and to the point

Count sold and remaining

Excel formula: Count sold and remaining
Generic formula 

If you have a list of items, and need to count how many you have total, how many are sold, how remain, etc., you can use the COUNTA function. This can be useful if you are selling tickets, seats, entries, or anything where you maintain and track an inventory of items sold. In the example shown, the formula in F7 is:


The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7:

COUNTA(B5:B11) // returns 7

The second COUNTA function does the same with the range C5:C11 and returns 3, since there are 3 non-blank cells in that range:

COUNTA(C5:C11) // returns 3

So, the entire formula is reduced to 7 - 3 and returns 4.

Note that in this case the values that appear in column C don't matter. They could be the the codes from column B (as in the example), the word "yes", or simply "x".

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

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.