COUNTIFS is a very important function when you want to count items with criteria.

By default, COUNTIFS criteria are related using AND logic.

For example, the below function will count all “Furniture” Products whose Sales Currency is “KES” i.e the two criteria have to be met for the count to happen.

=COUNTIFS(tblSales[Product],"Furniture",tblSales[Currency],"KES")

Now, how do you count All Furniture OR Technology sold using KES currency?

=SUM(COUNTIFS(tblSales[Product],I2:I3,tblSales[Currency],I4))

NB: The only trick to remember is that when using the OR criteria, highlight all criteria

Let us make the situation more complex, how do you count All Furniture OR Technology sold using KES or USD currency?

=SUM( COUNTIFS(tblSales[Product], TRANSPOSE(F2:F3),tblSales[Currency],H2:H3) )

The trick is to make your function 2D using the TRANSPOSE function.

DOWNLOAD WORKSHEET