Quick formula hacks with SUMIF and COUNTIF

Get 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.

EQ

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.

Excel Quicker!

Quick Tip

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])

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:

=COUNTIF(range, criteria)

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

=SUMIF(range,”>0″)

To add up all negative numbers only

=SUMIF(range,”<0″)

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:

=-SUMIF(range,”<0″)

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

=COUNTIF(range,”>0″)

To count the number of negative numbers only

=COUNTIF(range,”<0″)

To count the number of zeroes only

=COUNTIF(range,0)

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.


Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.