The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula.<\/p>\n
The above shows you different examples. You can have numbers in there separated by commas and it will add them together for you, you can have cell references and as long as there are numbers in those cells it will add them together for you, or you can have a range of cells with a colon in between the 2 cells, and it will add the numbers in all the cells in the range.<\/p>\n
2. COUNT<\/h3>\n
Formula: =COUNT(<\/span>A1:<\/span>A10)<\/p>\n The count formula counts the number of cells in a range that have numbers in them.<\/p>\n This formula only works with numbers though:<\/p>\n It only counts the cells where there are numbers.<\/p>\n **Learn more about the COUNT function in this on-demand, online course. FREE preview**<\/p>\n Formula: =COUNTA(<\/span>A1:<\/span>A10)<\/p>\n Counts the number of non-empty cells in a range. It will count cells that have numbers and\/or any other characters in them.<\/p>\n The COUNTA Formula works with all data types.<\/p>\n It counts the number of non-empty cells no matter the data type.<\/p>\n Formula: =LEN(<\/span>A1)<\/p>\n The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.<\/p>\n Notice the difference in the formula results: 10 characters without spaces in between the words, 12 with spaces between the words.<\/p>\n Formula: =TRIM(<\/span>A1)<\/p>\n Gets rid of any space in a cell, except for single spaces between words. I\u2019ve found this formula to be extremely useful because I\u2019ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP\u2019s.<\/p>\n I added in an extra space behind \u201cI Love Excel\u201d. The TRIM formula removes that extra space. Check out the character count difference with and without the TRIM formula.<\/p>\n Formulas: = RIGHT(<\/span>text, number of characters), =LEFT(<\/span>text, number of characters), =MID(<\/span>text, start number, number of characters).<\/p>\n (Note: In all of these formulas, wherever it says \u201ctext\u201d you can use a cell reference as well)<\/p>\n These formulas return the specified number of characters from a text string.RIGHT gives you the<\/span> number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.<\/p>\n I used the LEFT formula to get the first word. I had it look<\/span> in cell A1 and grab only the 1st character from the left. This gave us the word \u201cI\u201d from \u201cI love Excel\u201d<\/p>\n I used the MID formula to get the middle word. I had it look<\/span> in cell A1, start at character 3, and grab 5 characters after that. This gives us just the word \u201clove\u201d from \u201cI love Excel\u201d<\/p>\n I used the RIGHT formula to get the last word. I had it look<\/span> at cell A1 and grab the first 6 characters from the right. This gives us \u201cExcel\u201d from \u201cI love Excel\u201d<\/p>\n Formula: =VLOOKUP(<\/span>lookup_value, table_array, col_index_num, range_lookup)<\/p>\n By far my most used formula. The official description of what it does: \u201cLooks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify\u2026\u201d. (See the full explanation of VLOOKUP) Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).<\/p>\n Note: If at all possible use a number for the lookup_value. This makes it a lot easier to make sure the data you are getting back is a correct match.<\/p>\n If it finds a match of the \u201clookup_value\u201d in the left column of the \u201ctable_array\u201d it will return the value in the column you specify using the \u201cindex_num\u201d. The \u201cindex_num\u201d is relative to the left most<\/span> column. So, if you have the table_index look in column A and you want what is returned to be what\u2019s in column B the \u201cindex_num\u201d would be 2 because the leftmost column, column A in this case, is the 1st column in the table array and column B is the 2nd column (hence the 2 for the index number). If you want what is in column C to be returned you\u2019d put 3 for the index_num. The \u201crange_lookup\u201d is a TRUE or FALSE value. If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match. I only use FALSE when using the VLOOKUP formula.<\/p>\n Example:<\/p>\n You have 2 lists: 1 with a sales person\u2019s ID and the sales revenue for the quarter. Another with the sales person\u2019s ID and the sales person\u2019s name. You want to match up the sales person\u2019s name to the sales person\u2019s revenue numbers for the quarter. They are all jumbled around so to manually match this, even for a small number of salesmen would leave room for a high margin of error and take a lot of time.<\/p>\n The first list goes from A1 to B13. The 2nd list goes from D1 to E25.<\/p>\n In cell C1 I would put the formula =VLOOKUP(<\/span>B18, $A$1:<\/span>$B$13, 2, FALSE)<\/p>\n B18 = the lookup_value (the sales person\u2019s ID. This is a number that appears on both lists.)<\/p>\n $A$1:<\/span>$B$13 = the \u201ctable_array\u201d. This is the area I want the formula to search the leftmost column (column E in this case) for the \u201clookup_value\u201d. I went to F because if it finds match<\/span> in column E, I want it to return what\u2019s in column F. (The money signs are there so that the table_array will stay the same no matter where the formula is moved or copied to. This is called an absolute reference.)<\/p>\n 2 = the index_num. This tells the formula the number of columns away from theleft most<\/span> column to return in case of match<\/span>. So, if you find a match between the lookup_value and the leftmost column of the table array, return what\u2019s in the same row in the 2nd column of the table (the 1st column is always the leftmost column. It starts at 1, not 0).<\/p>\n FALSE= tells the formula I want it to only return the value if it\u2019s an exact match.<\/p>\n I would then copy and paste that formula along all the cells in column C next to the first list. This would give me a perfectly aligned list with the sales person\u2019s ID, sales person\u2019s revenue for the quarter, and the sales person\u2019s name.<\/p>\n In order to get a nice neat list of Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each other I used the VLOOKUP formula to compare 1 list<\/span> to another.<\/p>\n This is a complicated formula, but an extremely useful one. Check out some other examples: Vlookup Example, Microsoft\u2019s Official Example.<\/p>\n **Learn more about the VLOOKUP function in this on-demand, online course. FREE preview**<\/p>\n Formula: =IF(<\/span>logical_statement, return this if logical statement is true, return<\/span> this if logical statement is false)<\/p>\n When you\u2019re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.<\/p>\n Continuing with the sales example: Let\u2019s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: \u201cIF the salesperson met their quota, say \u201cMet quota\u201d, if not say \u201cDid not meet quota\u201d (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially when you get to more complicated things like Nested IF Statements in Excel).<\/p>\n It would look like this:<\/p>\n In the example with the VLOOKUP we had the revenue in column B and the person\u2019s name in column C (brought in with the VLOOKUP). We could put their quota in column D and then we\u2019d put the following formula in cell E1:<\/p>\n =IF(<\/span>C3>D3, \u201cMet Quota\u201d, \u201cDid Not Meet Quota\u201d)<\/p>\n This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.<\/p>\n Having the result right there from the IF statement is a lot easier than manually figuring this out.<\/p>\n Formulas: =SUMIF(<\/span>range, criteria, sum_range), =COUNTIF(<\/span>range, criteria), =AVERAGEIF(<\/span>range, criteria, average_range)<\/p>\n These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.<\/p>\n I use these formulas in our example<\/span> to see the average revenue (AVERAGEIF) if a person met their quota, Total revenue (SUMIF) for the just the sales people who met their quota, and the count of sales people who met their quota (COUNTIF)<\/p>\n A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol<\/span> in between the two cells. If I have \u201cSteve\u201d in cell A1 and \u201cQuatrani\u201d in cell B1 I could put this formula: =A1&\u201d \u201c&B1 and it would give me \u201cSteve Quatrani\u201d. (The \u201c \u201c puts a space in between what<\/span> you are combining with the &). I can use =concatenate(<\/span>A1, \u201c \u201c, B1) and it will give me the same thing: \u201cSteve Quatrani\u201d<\/p>\n Finding The Right Excel Formulas For The Job<\/p>\n There are 316 built in functions in Excel. You\u2019re not going to sit there and memorize what all of them do (or at least I hope not!). Luckily Excel has a built in wizard that helps you find the correct formula for what you\u2019re looking to do (if there is one).<\/p>\n Click the \u201cfx<\/span>\u201d next to the formula bar in Excel<\/p>\n This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the correct excel formula:<\/p>\n I typed in \u201cremove extra spaces\u201d and it returned the TRIM formula that we went over earlier.<\/p>\n There is so much more that I use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR, DAY, etc.), other formulas like AND and OR, along with many others.<\/p>\n The real power comes in combining these functions into complicated excel formulas.<\/p>\n","protected":false},"excerpt":{"rendered":" Don\u2019t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports. Excel Formulas You Should Definitely Know: 1. SUM Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5) … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-491","post","type-post","status-publish","format-standard","hentry"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/491","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/comments?post=491"}],"version-history":[{"count":1,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/491\/revisions"}],"predecessor-version":[{"id":492,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/491\/revisions\/492"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"https:\/\/lh4.googleusercontent.com\/0ufoieVor3e305u-rxUA82E_3OgaLZhMfKCIMegHNHZd78aJZXJX_VJQCCfqZfr9RNoaFH6-_w7kHnxEcwUPNL0pAk5IrMUK-60K7QV0X4fIg_m9y8Q3NjVnSQ\")
![\"\"](\"https:\/\/lh5.googleusercontent.com\/2sGbSmbXsggd9Xlee0C4Mzwt6ikvB1Oy5GTOCOxDDhXRdl2g_SssXqmnKFQQNmW7RLmFjx-iIWGSdXsGcZHUcSXI4dNPIXAACRcZYDPm_QAnTbKXyMftoDMdwg\")
3. COUNTA<\/h3>\n
![\"\"](\"https:\/\/lh3.googleusercontent.com\/JdTtlU3qO0cPPBdz74RcFzQYhZ1TRByGhaDgXsObjp2Z20_teVvDk8JSecbfP4nKH6_rwzkeB5jqc1KIYuR2ze02RspmU75yxFxvtRfsCFEQsQO33HmN940qWw\")
4. LEN<\/h3>\n
![\"len\"](\"https:\/\/blog.udemy.com\/wp-content\/uploads\/2013\/04\/len.png\")
<\/p>\n5. TRIM<\/h3>\n
![\"trim-formula-screenshot](\"https:\/\/blog.udemy.com\/wp-content\/uploads\/2013\/04\/trim-formula-screenshot-2.png\")
<\/p>\n6. RIGHT, LEFT, MID<\/h3>\n
\n<\/b><\/p>\n7. VLOOKUP<\/h3>\n
![\"\"](\"https:\/\/lh4.googleusercontent.com\/ICrcsRhxARFBGjiDTmZR_8vXk4OzwM4oyQAe0U4Nel6m4vGHmVmHs6LM4Ys9hJ5OORccQ1UTu0AbeQRFdXjx7McgnLgrlug7mbQKmcctVMtN8uowcUrxPRjX4Q\")
8. IF Statements<\/h3>\n
![\"\"](\"https:\/\/lh5.googleusercontent.com\/5wGmTYCtXU5EB0-7ATJrJdmMFOHh_CBknk_ng5PDIaUXhBOLU30F0HFa3BRnkfziNB3N6iHS6cF1UV0M8-78GVxjOwQVAknVdmNoqOLKO6Y8n9aOx1ja9B23pg\")
9. SUMIF, COUNTIF, AVERAGEIF<\/h3>\n
![\"\"](\"https:\/\/lh3.googleusercontent.com\/yNVtiE-fgI_bvjo-GZ1ZrzWU-rSmld2H9HS6iA7CuNGA9Akxdv3DV8j8-ZuvNpQsErk7QSrfmATpz5m9avQZE_w4Zc8VLkPsh3g1uQJ387a-MT2JDilQUh5tGw\")
10. CONCATENATE<\/h3>\n
\n<\/b><\/p>\n![\"\"](\"https:\/\/lh6.googleusercontent.com\/iZLDju21KqW07dND6Ukz7RvdbphamJasMne8reKfWMouqTtEMU3SgvDNIG4q3CzsRL8ByAItslna3kHKH6pWiC2ZB-azVthryyTV18fvtrsXsUhsgnMCj_j9gw\")
![\"\"](\"https:\/\/lh6.googleusercontent.com\/K1NY9aBc946fDhc6QI8q3_2PxjOHw2hGWNx55nddsFE_IYpK5bgcOvoMVje31Wp7Cmm_vbXHvb1EOWQDlmCa9rB-gdKC8jT_z3EEP9xKU4Sha8KYpHwSLQI1WA\")
More Excel Formulas<\/h3>\n
![\"\"](\"https:\/\/lh5.googleusercontent.com\/nRzvD2mZjXOv3tDdDsDnJmbcKCppet2fw-EbMtBIv-e1HlfRJ08Eg0HTMmfqaOyrbB5L3x7LeLYaHBv9l79SLKLwyHkXoEZlWnaIk0Mbak7MklM2ipwQk9HcSQ\")
![\"\"](\"https:\/\/lh3.googleusercontent.com\/biw5eFgJvDpFI9WQUkaGPNcSU0WvIs-yrfkiY-ivAWCb7-90TfV8PwLWLFB3A6LUmA81pv-4v0ctoJUwNQWTPwATn5T_7E_wIneAiuL-0Qj-mwSUEYseYOjrKg\")