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
| Function | Syntax |
|---|---|
| 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 Name | Syntax |
|---|---|
| sumifs | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) |
| countifs | COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) |
Suppose we have the following table.
| Sales Person | Number of order | Unit price | Revenue | Month |
|---|---|---|---|---|
| Daniel | 100 | 700 | 70000 | 01/01/2017 |
| Danny | 150 | 600 | 90000 | 01/02/2017 |
| Daniel | 170 | 550 | 93500 | 01/03/2017 |
| Daniel | 180 | 500 | 90000 | 01/04/2017 |
| Danny | 200 | 400 | 80000 | 01/05/2017 |
| Andy | 260 | 300 | 78000 | 01/01/2017 |
| Andy | 280 | 300 | 84000 | 01/02/2017 |
| Andy | 350 | 300 | 105000 | 01/03/2017 |
| Andy | 370 | 300 | 111000 | 01/04/2017 |
| Andy | 400 | 300 | 120000 | 01/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
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.
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.