Excel SUMIFS with Multiple Criteria and Wildcards 2022

We have a report of insurance policies recently sold. In the first column We have the Policy number, PolicyDate, Amount, Agent, client’s first, last name and address

The Sumifs function let’s you add anything based on one or multiple criteria, right here we only want to add the sales greater than 206000

Let’s use the SUMIFS to add only the sales made by Rebecca to the Allen family in Texas

We have three criteria or conditions: the Agent name, the client’s last name, and the state

In this cell let’s go ahead and type =sumifs(the sum range which is the column that we are adding, criteria range is the list where the first condition is, select the agent rebeca, then the list of client’s last names, click Allen, next lets get the list for the state, now let’s click on TX, close parenthesis and press enter

Let’s see if the result is correct if we add 250 and 205 the answer is 455k

Let’s review very quickly the syntax for the SUMIFS function. Sum_Range, What are you adding? Criteria_range? Where is your list of conditions? Criteria1 What is the condition that you are looking for? Criteria_range2 Where is your second list of conditions at? Criteria 2, What is your second condition? and we can go on with 127 range and criteria pairs.

In the second example, we want to add only the sales made in 2022 from Daniel Goldsmith, so here we need to set a condition greater or equal than a date, in this case 01/01/2022

We start with =SUMIFS press tab, now enter the sum range which is the amount column, then the criteria range, then the first criteria which is any cell with the name of Daniel in it,

Let’s select the second criteria range which is the date, and in quotes type “>= 1/1/2022”, close parenthesis and press enter

If you look at the table, there are two records with no agent name, these were the sales made by the main office.

To add empty cells as a condition, let’s type =sumifs(sum range, where is my list of criteria in the Agent column, and now let’s write “”, close parenthesis

There are two sales from the main office 120 and 150 each for a total of 270k

What is you want to search the opposite, or when the cell is not empty? Then add these two <> symbols inside the quote and it will add every agent in the list

To verify just add the 270 from the blank cells and it should give you the grand total of 1,524,000 as highlighted on the table

Please don’t forget to subscribe, like and share to get more Excel tips, also please comment below and thank you so much for your support!

Let’s kick it up a notch with the wildcards, First, we want to add the records whose city starts with the letter H, so we write the =SUMIFS(D6:D14,I6:I14,”h*”)

Wildcards work with text and not numbers. We are looking for policies in Pine street and the Agent ending with Potter

If we try to use the same “p*” it will not work, because the word is not at the beginning of the column

This time, we will write our SUMIFS function using the same rules as before, but this time we need to add two asterisks, one at the beginning and one at the end of Pine

In our last tip, if we need to add the amounts where the address ends with Ln, we can add the “*ln” and it will return the highlighted address in Provo, TX

Let us know in the comments which example did you like the most.

sumifs with multiple criteria in same column
sumifs multiple criteria different columns
sumifs with multiple criteria in same column google sheets
sumifs multiple criteria cell reference
advanced sumifs function in excel
sumifs multiple criteria column and row
if and sumif combined
how to use sumifs function in excel with multiple sheets
sumifs multiple criteria cell reference
sumifs multiple criteria column and row
if and sumif combined
advanced sumifs function in excel
sumifs with multiple criteria in same column
sumifs multiple criteria different columns
excel sumifs with multiple criteria
excel sumifs with multiple criteria in same column
sumifs function in excel with multiple criteria
sumifs formula in excel with multiple criteria
how to use sumifs in excel with multiple criteria
how to write sumifs formula in excel with multiple criteria
sumifs function in excel with multiple criteria horizontal and vertical
how do i sumifs with multiple criteria in the same column in excel
how to do sumifs in excel with multiple criteria
multiple sumifs in excel
how to use excel sumifs and sumif with multiple criteria
excel formula sumifs with multiple criteria