Use COUNTIFS and SUMIFS || How to Use COUNTIFS and SUMIFS in Microsoft Excel -2021 || 2024 Computer Tips

EXPERT EDUCATION September 10, 2024

Use COUNTIFS and SUMIFS

Excel has the useful functions COUNTIF and SUMIF which are able to count the number of records or sum values of a field based on a criteria. In the list below for example theses function calculate there are 10 ‘Full Time’ employees with a total salary of £604,760. Here are the formulas:

=COUNTIF(B3:B25,"Full Time")

=SUMIF(B3:B25,"Full Time",D3:D25)

Similarly the AVERAGEIF function would calculate the average salary for the Full Time employees:

=AVERAGEIF(B3:B25,"Full Time",D3:D25)

Since Excel 2021 there has been a corresponding set of functions ending with the letter S. COUNTIFS, SUMIFS and AVERAGEIFS. These functions allow for multiple criteria. For example, the number of Full Time employees with a job rating of 5.

The COUNTIFS function prompts for the first criteria range and first criteria (Status range B3:B25 and “Full Time”) followed by the second criteria range and second criteria (Job rating range and a rating of 5) Here is the function:

=COUNTIFS(B3:B25,"full time",C3:C25,5)

Similarly the SUMIF function calculates the total salary for the same two criteria.

=SUMIFS(D3:D25,B3:B25,"full time",C3:C25,5)

In this example there are 3 Full Time employees with a total salary of £151,210.

Excel allows a maximum of 127 range/criteria pairs.

Using COUNTIF with OR logic

When using COUNTIF S the criteria combine with AND logic. The more criteria used the fewer the records included. reducing the number of records being counted.

To combine criteria with OR logic conditions the simll add the COUNTIF functions together. For example to count Full Timers or Part Timers enter the formula as follows:

=COUNTIF(B3:B25,"full time",C3:C25,5) + COUNTIF(B3:B25,"part time ",C3:C25,5)

This results in finding 17 employees who are working Full time or Part Time.

Creating Tabulated data using SUMIFS

Rather than just calculating one result from a SumIfs it is possible to create tabulated data that allows a comparison to be made between all the Job Ratings and Status types.

To create tabulated data using the SUMIFS function first type all the different values as Row and column labels. Then click at the intersection point K3.and create the SUMIFS function:

Note about Partial Absolute Referencing

All the criteria ranges have Absolute references.
The Status criteria is partially Absolute where the row is fixed (K$2).
The Job rating criteria is partially Absolute where the column is fixed ($J3).

The full formula is:

=SUMIFS($D$3:$D$25,$B$3:$B$25,K$2,$C$3:$C$25,$J3)

It can be Autofilled or copied down and across to fill the table as follows.