MVP #38: Practical Use of SUMIFS and COUNTIFS in Excel

In an earlier post, I had explained how to use SUM, SUMIFS and COUNTIFS. I remember not including enough screenshots or including a practice Excel file in that post. 

Today, I'll be showing you a very practical use of SUMIFS and COUNTIFS functions in Excel and including a sample file.

Here is the Excel file with the data I'll be using for illustration.

It's the sales record of the number of Pizzas a QSR (Quick Service Restaurant) sold in the first opening hours of a particular day. 

If you look through the Excel file, you will see a sales record of 5,000 Pizzas sold.

Usually, the restaurant manager will want to see a summary report that totals the number of pizzas sold by type and total sales amount too. Something in a format similar to the one below.

In this case, a COUNTIFS and a SUMIFS will get us the summary report we want.

COUNTIFS will get us the number of each Pizza type sold while SUMIFS will get us the total sales amount made from each pizza type.

Here is the COUNTIFS function syntax --

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The part in the square brackets, [ .. ], are optional and should be used if you want to specify more that one criterion. 

For the sample file we are working on, to calculate the number of BBQ Chicken pizza sold, we'll enter the formula --

=COUNTIFS(B1:B5001,G3)

It is going to count the number of times BBQ Chicken occurs in the sales field (column B).

You can look through the sample file to see how to do for the other pizza types.


And for SUMIFS, here is the synatx --

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

And to calculate the total sales amount for BBQ Chicken, the formula is

=SUMIFS(C1:C5001,B1:B5001,G3)

 It's going add the sales amount in column C where the Sales item (column B) is BBQ Chicken.

It's that simple and powerful. You should attempt to do for the pizza types I haven't done for in the sample file.