A Guide to SUMIFS and COUNTIFS

Hello and welcome to A1 Excel! This post will cover SUMIFS and COUNTIFS which are fantastic for calculating quick and efficient summary points.

SUMIFS:

SUMIFS is relatively easy to follow in terms of summary because it is based on specific criteria. In this example I am showing how to gather specific sums of vet visits by year by pet type in a summary format. To note, in Column F of the example below I am using a formula to pull the Year out of the Visit Date in Column D. This can be done with the Text formula or a YEAR formula.

Example:

Result:

Here is the formula for reference if you would like to practice with it:

=SUMIFS($C$2:$C$10,$A$2:$A$10,$A13,$F$2:$F$10,B$12)

COUNTIFS:

COUNTIFS is similar to SUMIFS in the sense that it is based on specific criteria. However, instead of adding amounts together it counts items based on that specific criteria. The example below will showcase a count of pet types over the course of the years at the vet clinic. This is fairly similar to the SUMIFS summary as shown above.

Example:

Result:

Here is the formula for reference if you would to practice with it:

=COUNTIFS($A$2:$A$10,$A15,$F$2:$F$10,B$12)

These formulas are fairly customizable depending on your criteria and can hold many types of specifications. In addition to VLOOKUPS I have utilized SUMIFS and COUNTIFS often in my day-to-day work for pulling quick and easy summary data points.