Updated

Excel logical formulas: 12 simple IF statements to get started

Learn the ins and outs of the logical formulas like the famous IF Statements that represent the heart of Excel.

Today's Best Tech Deals

Picked by PCWorld's Editors

Top Deals On Great Products

Picked by Techconnect's Editors

1 2 Page 2
Page 2 of 2

=COUNTIF($B$4:$B$10, "=Roses")+COUNTIF($B$4:$B$12, "Daisies"); =COUNTIF($B$4:$B$10, "=Roses")+COUNTIF($B$4:$B$12, "Tulips"); =COUNTIF($B$4:$B$10, "=Tulips")+COUNTIF($B$4:$B$12, "Daffodils"); =COUNTIF($B$4:$B$10, "=Lillies")+COUNTIF($B$4:$B$12, "Daisies"); and =COUNTIF($B$4:$B$10, "=Lillies")+COUNTIF($B$4:$B$12, "Tulips")

I. Enter the following formulas in column I "Dollars," I4, I5, I6, I7, and I8:

=COUNTIF(C4:C12,"<=30"); =COUNTIF($C$4:$C$12,">=100"); =COUNTIF($C$4:$C$12,"<=75"); =COUNTIF($C$4:$C$12,"=50"); =COUNTIF($C$4:$C$12,"<=25")

011 use countif to countmatch cells in a range for selected dates dollars text JD Sartain / IDG Worldwide

Use COUNTIF to count+match cells in a range for selected dates, dollars, or text.

10. Use SUMIF to add the values in a range that meet your specific criteria.

Basically, you create a range (or column) of numbers, dates, or text that contains the data you want your criteria to match. The criteria are the specific value, date, or text that you want matched from that original range, and the sum range is the column that will be summed once the criteria is determined.

A. For example, in column A4:A19, enter the five different boroughs in New York: Manhattan, Brooklyn, Queens, The Bronx, Staten Island.

B. In column B4:B19, enter the bridges in the five boroughs.

C. In column C4:C19, enter the dollar amount for the tolls collected on each bridge in each borough.

D. In column D4:D8, enter the first criteria you want matched; for example, Manhattan, Queens, the Bronx, etc.

E. In column E4:E8, enter the SUMIF formulas: =SUMIF($A$4:$A$19, D4, $C$4:$C$19) for E4:E8.

F. Use the Function key F4 to make the range (column A) and sum range (column C) absolute, but not the criteria column D. Then all you have to do is copy the formula down.

G. In column E9:E13, enter the first criteria you want matched, then enter a second criteria in F9:F13. Next, enter the SUMIF formula: =SUMIF($A$4:$A$20,D9,$C$4:$C$19) + SUMIF($A$4:$A$20,F9,$C$4:$C$19) for E9:E13 and copy down to E13. Notice that the criteria is matched from Criteria1 (column D) and Criteria2 (column F).

H. In column E14:E19, enter the SUMIF “hardcoded” formula (that just means the criteria is coded inside the formula: =SUMIF($A$4:$A$19,"Brooklyn",$C$4:$C$19) + SUMIF($A$4:$A$19,"Queens",$C$4:$C$19) and copy down to E19, then change the criteria in each formula to match the data in D14:D19.

NOTE: That the criteria can be entered in a separate cell off to the side or hardcoded into the formula. The benefit of using a separate cell means you can easily change the criteria without rewriting the formula. Obviously, G (above) is much easier and makes more sense than hardcoding (H) the criteria inside the formula, but there may be a situation in the future where the hardcoded solution is a better fit.

012 use sumif to add values in a range that meet specific criteria JD Sartain / IDG Worldwide

Use SUMIF to add values in a range that meet specific criteria.

11. Use AVERAGEIF to average the values in a range that meet your specific criteria.

AVERAGEIF is just like SUMIF except that you average the numbers instead of summing them. The syntax for this formula is: =AVERAGEIF (range, criteria, average_range).

A. Enter 17 college subjects in column A4:A20.

B. Enter the corresponding genres in column B4:B20.

C. Enter the number of students enrolled in each subject & genre in column C4:C20.

D. Enter the criteria you want matched to get the average number of students in each subject in D4:D9.

E. Enter the formula in E4:E9; that is: =AVERAGEIF($A$4:$A$20, D4, $C$4:$C$20). Copy the formula down through E9.

F. Enter the Criteria1 plus the Criteria2 you want matched to get the average number of students in two specified subjects in D10:D15 and F10:F15.

G. Enter the formula in E10:E15; that is: = =AVERAGEIF($A$4:$A$20,D10,$C$4:$C$20) + AVERAGEIF($A$4:$A$20,F10,$C$4:$C$20). Copy the formula down through E15.

H. In column E16:E20, enter the AVERAGEIF “hardcoded” formula, which means the criteria is coded inside the formula: =AVERAGEIF($A$4:$A$20,"History",$C$4:$C$20) + AVERAGEIF($A$4:$A$20,"Geography",$C$4:$C$20) and then copy down to E20, and change the criteria in each formula to match the data in D16:D20.

013 use averageif to average values in a range that meet specific criteria JD Sartain / IDG Worldwide

Use AVERAGEIF to average values in a range that meet specific criteria.

12. Using Wildcards with IF statements

A. The asterisk (*) represents any number of characters; for example, op* could mean operator, opposition, opulent, opportunity, etc.

B. The question mark (?) represents a single character; for example, m?th could be math, moth, meth, myth, etc.

C. The tilde (~) identifies a wildcard character (~ * ?) in the text that’s separate from the keyboard character; that is, the asterisk as a symbol as opposed to a wildcard. For example, if you wanted to find the exact characters in the word 'moon' followed by an asterisk (moon*), this search string would return all words with 'moon' at the beginning, followed by any/all other words with the root word 'moon' such as moonshine, moonlight, moonstone, etc.). To locate the word 'moon' followed by an asterisk, use the tilde (~) to tell Excel that the asterisk (in this case) is NOT a wildcard. The syntax would look like this: moon~*.

D. In column A3:A21, enter some related items such as Wally World, Sea World, Fairy Worlds, etc.

E. In column B3:B11, enter the following formula using the asterisk (*) wildcard: =COUNTIF($A$3:$A$21,"*world*"), then copy the formula down through B11 changing the criteria word for each formula such as “*land*”; “*Fairy*”; “*Sea*”; etc.

F. In column C3:C21, enter nine different items of clothing. Because there are 19 rows, many items will be repeated.

G. In column D3:D21, enter the cost for each item in column C.

H. In column E3 through E11, enter the following formula using the asterisk (*) wildcard: =SUMIF($C$3:$C$21,"*Suits*",$D$3:E3). Copy the formula down to E11, then change the criteria words to Jackets, Jeans, Sweaters, Skirts, Vests, Gloves, Dresses, and Shoes or choose your own criteria words.

I. In column E12 through E21, enter the following formula using the asterisk (*) wildcard: =SUMIF($C$3:$C$21,"*Shoes*",$D$3:$D$21) + SUMIF($C$3:$C$21,"*Jeans*",$D$2:$D$9), then change the criteria words to Shoes & Jeans, Jackets & Vests, Skirts & Sweaters, Dresses & Shoes, Skirts & Vests, Jeans & Jackets, Suits & Gloves, Sweaters & Vests, Shoes & Gloves, and Shoes & Suits or choose your own criteria words.

014 using wildcards with if statements JD Sartain / IDG Worldwide

Using Wildcards with IF statements

Editor's note: This article was updated from its original posting on September 8, 2015.

Note: When you purchase something after clicking links in our articles, we may earn a small commission. Read our affiliate link policy for more details.
1 2 Page 2
Page 2 of 2
  
Shop Tech Products at Amazon