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:

For the criteria, you can enter it directly in the formula or you can use a reference to a cell containing the criteria.

If you use the third argument (the sum_range), then:

• the evaluation range is assessed against the criteria; and
• the corresponding cells from the sum_range are added (for those cells in the evaluation range that meet the criteria).

If you omit the third argument, the evaluation range itself is used as both the evaluation range and the sum_range.

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.

You can fix this quickly using the tips Quick access to cell formatting and Clear cell formatting.