Excel Help : Using COUNTIF() and SUMIF() formulas, examples | Chandoo. Posted on November 1. Learn Excel - 8. 3 comments. If for every countif() I write excel paid me a dollar, I would be a millionaire by now. Microsoft Excel Formulas. Learn Microsoft Excel Formulas Free With Ozgrid. Free Excel Formulae Training. I have used the sumif formula many times ofer the years. However, I have always only refrences one sheet. I am now tying to refrence several sheets. B. ![]()
![]() It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series. Using COUNTIF() to replace pivot tables: We all know that you can use countif() to replace pivot tables for simple data summarization. For eg. if you have customer data in a table and you would like to know how many customers you have in each city you can use countif() to find that. More on this method of using countif and 4 other ways of using excel if () formulas. Counting Valid Phone Numbers in a Range: Using operators < and > in countif() you can findout valid phone numbers in range like this: countif("data- range","> "& 1. Finding number of customers in a city based on their phone number: This trick may not work perfectly. We can use countif("data- range","2. Mumbai (since all Mumbai phone numbers begin with 2. Note: This method works as long as phone numbers have identifiable calling codes and stored as text. To covert a number to text you can use text() or append an empty space to the number. Pattern matching: Often when you extract data from other sources and paste it in excel it is difficult to process it when the formats are not consistent. For eg. when you copy address data of a bunch of customers and need to know how many customers are in “New York” you can use countif like this: countif("data range", "*new york*"), the operator * tells excel to match any cell with new york in it, not necessarily at the beginning or end of the cell. Counting positive numbers in a range: Again we use the > operator to count the positive numbers in a range like this: countif("data- range","> 0"). A very good use of this trick is when you need to calculate average of a bunch of numbers but need to exclude zeros: sum("data- range")/countif("data- range","< > 0")As a replacement to FIND(): Excel FIND() is powerful formula to find if a particular text occurred in another text. But one problem with find is it returns #value! What if all you need to know was whether your cells had a particular value or not? You are right, you can use COUNTIF() for that too, like: countif("cell- you- want- to- look","*hilton*") will return 1 or 0. For sorting text: Read more on this at sorting text using excel formulas. Findout the number of errors in a sheet: The beauty of countif() is that you can even count error cells. For eg. you can use it like: =COUNTIF(1: 3. VALUE!") to findout how many #VALUE! This can be useful if you are building a complex model and need to keep track of errors. Most of the tricks should work with SUMIF() as well. If you like this, read the other posts in the spreadcheats series. It is a 3. 0 post series (3 posted so far) that aspires to make YOU very good in using excel to solve day to day problems. Introducing our Online Power BI Class: Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date. Click here to know more and join us. PS: Hurry up. Enrollments are closing tonight. Share this tip with your friends. Written by Chandoo. Tags: countif(), howto, Learn Excel, microsoft, Microsoft Excel Formulas, MS, spreadcheats, spreadsheets, sumif(), text processing, tips, tricks. Home: Chandoo. org Main Page? Doubt: Ask an Excel Question.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |