Quick formula hacks with SUMIF and COUNTIFGet more flexibility and speed from SUMIF and COUNTIF functions, whether you are new to them or use them already. Want to sum only the positives or negatives from a combined list of numbers? Need to add or count items based on matching an account number, date, or description? Here's a quick way to do all this and more.
This tip gives you a better way to use SUMIF and COUNTIF for faster spreadsheets in more situations.
The Quick Tip shows how to add or count only the positive or negative numbers from a combined list.
The Bonus Tip gives you more power to evaluate numbers using criteria.
With the Expert Tip you can extend that power to any type of cell content for faster and more flexible evaluations.
We hope you find these tips useful.
Using conditions with SUMIF and COUNTIF
This tip will show you how to get more flexibility and speed from SUMIF and COUNTIF functions, whether you are new to them or use them already.
Once you start using this tip, you will use it a LOT!
Revisiting SUMIF and COUNTIF
First, let’s recap the SUMIF and COUNTIF functions.
(You can skip to the next section if you are already familiar with these functions).
Using the SUMIF function
You use SUMIF to add up only the cells that meet a certain criteria.
The syntax for the SUMIF function goes like this:
=SUMIF(range, criteria, [sum_range])
criteria, you can enter it directly in the formula or you can use a reference to a cell containing the
If you use the third argument (the
- the evaluation
rangeis assessed against the
- the corresponding cells from the
sum_rangeare added (for those cells in the evaluation
rangethat meet the
If you omit the third argument, the evaluation
range itself is used as both the evaluation
range and the
Using the COUNTIF function
The COUNTIF function counts the cells that meet a certain criteria.
It works just like SUMIF, except:
- COUNTIF returns the number of matching cells, rather than the sum of them added together; and
- COUNTIF has only two arguments (it does not have an optional third argument).
The syntax for the COUNTIF function goes like this:
Positive and negative number totals with SUMIF
When you have a list of numbers, you can easily make seperate subtotals for the positive and negative numbers.
Just use the following formulas (be sure to replace
range with a reference to the cell range containing your number list):
To add up all positive numbers only
To add up all negative numbers only
If you want the answer to be in the form of a positive number, put a negative sign (hyphen) in front of the function, like this:
To add up all zeroes only
You don’t need to use a formula to add up the zeroes. Their total will always be zero!
Count positive and negative numbers with COUNTIF
You can also count how many numbers are positive and how many are negative, like this:
To count the number of positive numbers only
To count the number of negative numbers only
To count the number of zeroes only
When would I want to separate positive and negative numbers?
Here are just a some of the possible uses for this technique:
- Debits and credits
- Receipts and payments
- Invoices and credit notes
- Profits and losses
- Check-ins and check-outs
- Positive and negative variances
- Upside and downside deviations
- Applications and redemptions
- Deposits and withdrawals
- Usages and replenishments
- VAT supplies and claims
- Increases and decreases
- Upvotes and downvotes
- Likes and dislikes
You should easily be able to find situations where using this tip can save you time summarizing a list of positive and negative numbers!
Troubleshooting positive and negative number summaries made with SUMIF or COUNTIF
If for some reason your numbers are formatted as text, these formulas will not work properly. If you get unexpected results, check that your list of numbers is not stored as text.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
Download files for this tip
Get one for free.