Excel SumIf examples, with multiple criteria, conditional on text array, wildcards and date
Chapters:
0:00 Table of Contents
0:10 Omit the third argument Sum_Range and use greater than, equal to and less than
0:44 Conditioning with text and array text
1:32 Excel SumIf multiple criteria
1:58 Combining conditions with wildcards
2:25 Combine conditions with an asterisk "*"
3:16 Condition with date
3:48Excel SumIf syntax
Related articles:
Excel SumIf function with ?/*, Average and array multiple criteria and different ranges(13 examples)
http://www.liangshunet.com/en/201905/...
Our website: http://www.liangshunet.com/pub/items....
Description:
Excel SumIf examples, with multiple criteria, conditional on text array, wildcards and date
Note: The cc should be changed to greater than sign, the ee should be changed to less than sign in the formula.
The SumIf function is used to sum within the range that meets the specified conditions in Excel. It consists of three arguments, of which the first and second arguments are required and the third argument is optional. The following is how Excel SumIf function uses text array, wildcards and date as conditions.
One. Excel SumIf syntax
Expression: SUMIF(range, criteria, [sum_range])
Description:
A. The first and second arguments Range and Criteria are required in the expression, and the third argument [sum_range] is optional; if [sum_range] is omitted, find the sum in the Range; if there is [sum_range], the sum in [sum_range].
B. Wildcards "question mark (?) and asterisk (*)" can be used in the second argument summation criteria, the question mark matches any single character, and the asterisk matches any one or a string of characters; if you want to find "? and *", you need to add the escape character ~ in front of them, for example, to find ?, you need to write ~?.
Two. Examples of how to use the Excel SumIf function
(1) Omit the third argument Sum_Range and use greater than, equal to and less than
1. Suppose you want to find the sum of the sales of all clothes with their sales greater than 500. Select cell D9, copy the formula =SUMIF(D2:D8,"cc500") into it, press Enter on your keyboard, and return to 5279.
2. The condition "cc500" in the formula =SUMIF(D2:D8,"cc500") can also be written like this: "cc"&500.
(2) Condition with text and array text
1. If you want to find the sum of the sales of all "women's clothing". Copy the formula =SUMIF(B2:B8,"Women's clothing",D2:D8) to cell D9, press Enter to calculate their sum.
Note: The condition "Women's clothing" in the formula =SUMIF(B2:B8,"Women's clothing",D2:D8) can also be replaced by a cell, that is, the formula can be changed to =SUMIF(B2:B8,B2,D2:D8).
2. If you want to find the sum of all "non-women's" clothing sales. Copy the formula =SUMIF(B2:B8,"eeccWomen's clothing",D2:D8) to cell D9, press Enter to return the calculation result 1827.
3. Excel SumIf multiple criteria. Suppose you want to find the sum of all clothing sales of "T-shirt" and "Chiffon" in "Secondary classification". Copy the formula =SUM(SUMIF(C2:C10,{"T-Shirt","Chiffon"},E2:E10)) to cell E11, press Enter, you will get the reslut.
In the formula =SUM(SUMIF(C2:C10,{"T-Shirt","Chiffon"},E2:E10)), use the array text "T-shirt", "Chiffon" as the conditions.
(3) Combining conditions with wildcards
1. Use the question mark "?" to combine conditions
A. Copy the formula =SUMIF(C2:C10,"????????????????",E2:E10) to cell E11, and press Enter to calculate the sum of sales of all Women's clothing.
B. A question mark (?) represents any character, 16 question marks "????????????????" in the formula =SUMIF(C2:C10,"????????????????",E2:E10) represents 16 letters, that is, the sum of all clothing sales with 16 letters in the "classification" is calculated.
2. Combine conditions with an asterisk "*"
A. Suppose you want to find the sum of the sales of all clothes ending in "shirt". Copy the formula =SUMIF(A2:A10,"*Shirt",E2:E10) to cell E11, press Enter, you will get their sum.
B. If you wanted to find the sum of the sales of all clothes starting with a certain word (such as "white"), the formula can be written as follows: =SUMIF(A2:A10,"White*",E2:E10); If you want to find the sum of the sales of all clothes that contain a certain word or phrase (such as "white"), the formula can be written as =SUMIF(A2:A10,"*White*",E2:E10).
(4) Condition with dat
1. Copy the formula =SUMIF(E2:E10,"ee5/4/2022") to cell E11,press Enter to calculate the sum of all clothing sales.
2. There is no date field in the table, but Excel will automatically compare the system time with the date "5/4/2022" in the condition. The calculation time is 5/4/2022, so the condition is established. Therefore, the statistics show the sum of all clothing sales.
If you want to calculate the sum of all clothing sales with empty conditions, the formula can be written like this: =SUMIF(E2:E10,"").
Смотрите видео Excel SumIf examples, with multiple criteria, conditional on text array, wildcards and date онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Loinsure 08 Май 2022, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 48 раз и оно понравилось 0 людям.