< Go Back

Count sold and remaining

★ 20 minute read

Just as it is with using other formulas in Excel to solve many different issues, you can also use Excel to count the number of sold and remaining items on a list. This post will guide you on how to count sold and remaining items on a list using a formula approach in Excel.

Count sold and remaining

Formula

The general formula showing how to Count sold and remaining is:

=COUNTA (rng 1)-COUNTA (rng 2)

Explanation

If you would need to count how many you’ve sold and the total you have left from a list of items, the COUNTA function is your best choice. It is very helpful if you are selling seats, commodities, tickets or anything where you track inventory of the sold items.

Example 1

 

 

Figure 1. Count sold and remaining

 

In figure 1 above, the formula in cell G10 is:

=COUNTA(C5:C11)-COUNTA(D5:D11)

The first COUNTA function in the formula counts the non-blank cells in C5:C11 and returns 7 using the formula:

=COUNTA(C5:C11)

The second COUNTA function in the formula does the same in D5:D11. This function returns 2 (since there are 2 non-blank cells) using the formula:

=COUNTA(D5:D11)

The result is then processed to return “5” i.e. 7- 2.

Notes

Any non-blank cells that contain text or numbers are counted.
The values listed in column D are irrelevant. They could be “yes” to mean sold, an “X” mark or codes.

To ensure that the value in column D corresponds to values in column C in the same row, you can apply the SUMPRODUCT function instead.
=SUMPRODUCT(--(C5:C11=D5:D11))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar