SUMIF, SUMIFS, COUNTIF and COUNTIFS For Multiple Values of Interest At Once

In case you are not too familiar with SUMIF, SUMIFS, COUNTIF and COUNTIFS, they are a group of formula used to sum or count records that meet a specified criterion. 

Below are some easy to follow examples to help you fully grasp them. You can download the practice file here: https://drive.google.com/file/d/15W7zluKlqD3naEyIskvF4xT9wZ22qvYZ/view?usp=sharing 

SUMIF
I have a transaction database for a pizza restaurant and I am interested in total sales for Hot Veggie. With SUMIF I can get that done easily.

=SUMIF(B:B,"Hot Veggie",E:E)

SUMIFS

What if we also want to consider other conditions beyond just the name of the pizza sold, say we want to find the sales amount for all the Hot Veggie sold after 8:30 am? 

That is what SUMIFS is for; it is the plural form of SUMIF, allowing you to chain as many conditions as you want.

=SUMIFS(E:E,B:B,"Hot Veggie",F:F,">8:30")

COUNTIF

Maybe I am just interested in the number of transactions that are for Hot Veggie. Again, very easy. We just use COUNTIF.

=COUNTIF(B:B,"Hot Veggie")

COUNTIFS

How about count of transactions for Hot Veggie that happened after 8:30?

=COUNTIFS(B:B,"Hot Veggie",F:F,">8:30")

Now to the very interesting parts. What if I am interested in not just Hot Veggie, but also Meatzaa, Italiano and BBQ Chicken?

Q: Sum all the sales for Meatzaa, Hot Veggie, Italiano and BBQ Chicken

A: =SUM(SUMIF(B:B,{"Meatzaa","Hot Veggie","Italiano","BBQ Chicken"},E:E))

You might wonder why the {..} wrapping the list of pizzas. It is how Excel allows you to specify an array (a list, like we've done). Also, you might wonder why the SUM(...) around the SUMIF formula. It is what allows Excel to sum up all the different answers for each pizza type. See what I mean from the partially calculated formula screenshot below.

And you can use this same pattern for SUMIFS, COUNTIF and COUNTIFS.

Q: Sum all the sales for Meatzaa, Hot Veggie, Italiano and BBQ Chicken after 8:30 am

A: =SUM(SUMIFS(E:E,B:B,{"Meatzaa","Hot Veggie","Italiano","BBQ Chicken"},F:F,">8:30"))

Q: Count of all the transactions for Meatzaa, Hot Veggie, Italiano and BBQ Chicken

A: =SUM(COUNTIF(B:B,{"Meatzaa","Hot Veggie","Italiano","BBQ Chicken"}))

Q: Count of all the transactions for Meatzaa, Hot Veggie, Italiano and BBQ Chicken after 8:30 am

A: =SUM(COUNTIFS(B:B,{"Meatzaa","Hot Veggie","Italiano","BBQ Chicken"},F:F,">8:30"))

For more of these types of very useful tutorials you can check out https://www.urbizedge.com/Tutorials and subscribe to my YouTube channel