IF, COUNTIF, SUMIF, TODAY, joining text, cleaning text, ROUND, and XLOOKUP
A function is a ready-made formula with a name, like SUM. Here are the most useful ones after SUM. The parts inside the brackets are separated by commas.
IF checks something and gives one answer if it is true and another if it is false.
=IF(C2>=100,"Paid in full","Owes money")
Read it like this: if C2 is 100 or more, show "Paid in full". If not, show "Owes money".
=COUNTIF(D2:D20,"Paid") counts how many cells in D2 to D20 say Paid.
=COUNTIF(C2:C20,">50") counts how many numbers are over 50.
=SUMIF(D2:D20,"Paid",C2:C20) looks in D for the word Paid, and adds up the matching amounts in C.
Read it like this: where to look, what to look for, what to add.
=TODAY() shows today's date. It updates every day by itself. The brackets stay empty.
To see how many days until a date in B2: =B2-TODAY()
Say A2 has Sara and B2 has Klein.
=A2&" "&B2 gives Sara Klein. The " " adds a space in the middle.=CONCAT(A2," ",B2) gives the same thing.=TRIM(A2) removes extra spaces before, after, and in the middle.=PROPER(A2) makes each word start with a capital: sara klein becomes Sara Klein.=UPPER(A2) makes ALL CAPITALS. =LOWER(A2) makes all small letters.=ROUND(B2,2) rounds to 2 numbers after the dot, like money. 12.3456 becomes 12.35.
=ROUND(B2,0) rounds to a whole number.
XLOOKUP is like looking up a name in a phone book. You give it what to find, where to find it, and what to bring back.
Say a price list has items in A2:A20 and prices in B2:B20. To find the price of the item typed in E2:
=XLOOKUP(E2,A2:A20,B2:B20,"Not found")
Read it like this: find E2, in the items, bring back the price. If it is not there, show "Not found".
=XLOOKUP(E2,$A$2:$A$20,$B$2:$B$20,"Not found")Older Excel (2019 and before) has no XLOOKUP. Use VLOOKUP instead:
=VLOOKUP(E2,A2:B20,2,FALSE)
Read it like this: find E2 in the first column of A2:B20, bring back column 2, and FALSE means exact match only. Always type FALSE at the end.
Go to the sheet 5 Customers. In F2 type =IF(D2>=E2,"Paid","Owes") and copy it down. In I2 count how many are Paid with COUNTIF. In I3 add up what was paid by Paid customers with SUMIF. In G2 clean the messy name with =PROPER(TRIM(A2)) and copy it down. On the sheet 5b Lookup, use XLOOKUP in B3 to find the price of the item typed in A3.
1. What does =IF(B2>10,"Big","Small") show when B2 is 4?
Small, because 4 is not bigger than 10.
2. What does =COUNTIF(A1:A10,"Yes") do?
Counts how many cells from A1 to A10 say Yes.
3. How do you join a first name in A2 and a last name in B2 with a space?
=A2&" "&B2
4. What do the three main parts of XLOOKUP mean?
What to find, where to look, and what to bring back.
Excel Made Easy. Free course. Written for easy reading.