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.
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.
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 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:
=F5-F6 // use existing values
In other words, we simply re-use existing results. This minimizes the number of calculations performed and reduces errors.
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:
In this example, the goal is to compare two columns and return the count of matches in corresponding rows. A good way to solve this problem is to use the SUMPRODUCT function or the SUM function, as explained below. SUMPRODUCT function The SUMPRODUCT...
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one...
COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...
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.