How to Use COUNTIF, SUMIFS, and MINIFS Correctly in Excel
Question details
The user needs to correctly apply COUNTIF, SUMIF/SUMIFS, or MINIFS to calculate usage based on whether data represents remaining levels or usage amounts.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating total consumption or remaining inventory levels from a dataset containing specific criteria.
- Observed behavior
- The user attempted to calculate total usage using COUNTIF, which resulted in a count of reading entries rather than the actual sum or difference of the inventory levels.
Determine whether your data column contains the actual usage amounts or the remaining inventory levels, as this dictates whether you must use SUMIFS or MINIFS.
Calculate Usage from Remaining Levels Using MINIFS
Use this method when your dataset logs the remaining amounts in an inventory, and you need to find the total consumed from a known starting capacity.
When readings decline from a starting value (e.g., 150) to zero, you calculate consumption by finding the lowest remaining level and subtracting it from the starting capacity.
Determine the maximum or starting capacity of your inventory or tank (e.g., 150).
Select the cell for your result and type the formula: =150-MINIFS(H3:H20, B3:B20, "N. Well").
Press Enter. The formula will find the lowest recorded remaining level for "N. Well" in column H and subtract it from 150 to calculate the total usage.
Calculate Total Consumption Using SUMIFS
Use this method when your dataset logs the actual amounts used during each reading rather than the remaining balance.
Count Matching Readings Using COUNTIFS
Use this method only when you need to know how many times a reading was taken, not the total sum of the readings.
Master Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and math functions like COUNTIFS, SUMIFS, and MINIFS. It provides an intuitive formula builder and seamless compatibility with Microsoft Excel workbooks, making data analysis straightforward and error-free.
- 1. Open data in WPS Spreadsheet: Launch WPS Office and open your dataset containing the inventory or usage logs.
- 2. Access the Formula tab: Navigate to the 'Formulas' tab on the ribbon and click on 'Insert Function'.
- 3. Input arguments visually: Search for SUMIFS or MINIFS, and use the dialog box to select your sum range and criteria ranges without worrying about syntax errors.
Frequently Asked Questions
Why does COUNTIF return a small number instead of my total usage?
COUNTIF only counts the number of cells that meet your criteria (e.g., how many times a reading was logged). It does not sum the numerical values inside those cells. You must use SUMIF or SUMIFS to add values together.
What is the difference between SUMIF and SUMIFS?
SUMIF evaluates a single condition, while SUMIFS can evaluate multiple conditions across different ranges. Additionally, the syntax order differs: SUMIFS requires the sum range first, whereas SUMIF places the sum range last.
Why is my MINIFS formula returning an error?
Common causes for MINIFS errors include mismatched range sizes (e.g., the criteria range is B3:B20 but the min_range is H3:H21) or forgetting to enclose text criteria in quotation marks. Ensure all ranges align perfectly and criteria are properly formatted.
