How to do summation or counting in Excel with multiple wildcard criteria? – new excel functions

We can use the following functions to do summation or counting in Excel with multiple wildcard criteria

Summation or counting in Excel with multiple wildcard criteria

FunctionSyntax
Sumif

=Sumif( , , )

Countif

=countif(, )

However, both Sumif and Countif only allows single criteria.

SUMIFS & COUNTIFS

Since Excel 2007, Microsoft have introduced two new functions called SUMIFS & COUNTIFS

They are close to sumif and countif function. However, they allows multiple criteria.

Following is the syntax

Function NameSyntax
sumifsSUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
countifsCOUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Suppose we have the following table.

Sales PersonNumber of orderUnit priceRevenueMonth
Daniel1007007000001/01/2017
Danny1506009000001/02/2017
Daniel1705509350001/03/2017
Daniel1805009000001/04/2017
Danny2004008000001/05/2017
Andy2603007800001/01/2017
Andy2803008400001/02/2017
Andy35030010500001/03/2017
Andy37030011100001/04/2017
Andy40030012000001/05/2017

COUNTIFS

Now we need to work out how many months does all sales person that the name start with “Dan” have over 160 orders. This means, no matter he is Danny or Daniel, as long as they have over 160 orders in a particular month, it should be counted.

To do this, I am using he COUNTIFS function

Example of countifs()
Example of countifs()

To easy demonstration, I added three rows (14 – 17) to allow user to enter the criteria.

In the Sales person to check, I add a “*”to indicate wildcard search.  The “*” means something.

Following my countifs function

=COUNTIFS(

A3:A12,         <–   Range for criteria 1

A16,              <–   criteria 1 (i.e. “Dan*”)

B3:B12,         <–   Range for criteria 2

“>”&B16       <–   criteria 2 (Value in B3:B12 is greater than the value stated in B16)

)

SUMIFS

Now we try to calculate the total revenue for all salesperson that the name start with “Dan” has over 160 orders in all months. This means, no matter he is Danny or Daniel, as long as they have over 160 orders in a particular month, the revenue should be included in our result.

To do this, I am using the SUMIFS function.

Example of sumifs function
Example of sumifs function

Following is my function:

=SUMIFS(

D3:D12,          <– The range to sum

A3:A12,         <–   Range for criteria 1

A16,,             <–   criteria 1 (i.e. “Dan*”)

B3:B12,         <–   Range for criteria 2

“>”&B16       <–   criteria 2 (Value in B3:B12 is greater than the value stated in B16)

)

Those functions are also available from excel in Ipad.

Tags: excel math formulas tutorial, sumif, sumifs, countif, countifs, MS-Excel 2010, excel 2007, excel 2013, excel 2016, wildcard, wildcard criteria, Excel 365, Office, multiple criteria

Leave a Comment