The SUMIF, COUNTIF and CONCATENATE functions are Excel’s
built-in functions. These functions can also be applied practically to business
systems.
This is the Part 3 of chapter 2 of Excel tutorial. You are
advises to read the first two parts:
In this part 3 of chapter to of Excel tutorial, I will explicitly explain the hidden details of SUNIF, COUNTIF, CONCATENATE with practical illustrations.
COUNTIF FUNCTION
This is one of MS-Excel’s Powerful Statistical functions
that counts the total number of cells that met a specified condition within a
specified range of cells.
The syntax for the COUNTIF function is =COUNTIF(Range, Criteria) press Enter.
The function takes two arguments:
1. Range: This is the range of cells excel will count if the meet the given condition.
2. Criteria: This is the specified condition a cell must match before Excel can count it.
Practical
Illustration of the Excel COUNTIF Function
The table shown in the worksheet below is used to for
recording the weekly sales of Joe-Links Business Resources for the first six
weeks of the year 2016. Use the COUNTIF function to count the number of weeks for
each month where the amount of sales made is
not less than $20000.
 |
COUNTIF function – Table 1
|
SOLUTION TO THE COUNTIF QUESTION
1. Prepare a worksheet like the one shown in the figure above.
2. Place your cell pointer in cell F3 and type the following syntax =COUNTIF(B3:E3,”>=20000”) press Enter. Take note of the quote in the syntax. It is mandatory.
B3:E5 is the range of cells for the four weeks of the month January.
“>=20000” is the criteria a cell in the range must meet before Excel counts it.
Don't copy these syntaxes. Type them yourself.
3. Use the auto fill handle to fill the subsequent spaces in the column.
The figure below contains the completed table of the question above.

SUMIF FUNCTION
This is another MS-Excel’s powerful function that adds the
value in the cells within a specified range that met a specified criterion with
respect to the values of another specified range. The SUMIF function is similar
to the COUNTIF function, but requires two ranges of data to be specified.
The syntax for the SUMIF function is =SUMIF(Range,Criteria,Sum_range) press Enter.
The function takes three arguments. The first two – The Range and Criteria are the same as in the COUNTIF function. The third argument
is the Sum_range which is the range
of cells containing the values you wish to sum with respect to the set
criteria.
Practical
Illustration of the Excel SUMIF Function
The table contained in the figure below shows a list of
individuals who ordered goods from Joe-Links Services and their payment status.
In the Status column, PAID indicates
individuals that paid for the goods they received. While NOT PAID indicates
individuals that have not. Using the SUMIF function, calculate the Total Amount Paid and the Total Amount Owed by the customers.
SOLUTION TO THE SUMIF
QUESTION
1. Prepare your worksheet like the one shown in the figure above.
2. Type in the following syntax in cell B9 =SUMIF(D3:D7,”PAID”,C3:C7) press Enter.
3. Type in the following syntax in cell B10 =SUMIF(D3:D7,”NOT PAID”,C3:C7) press Enter.
4. D3:D7 is the range of cells you want to check for PAID and NOT PAID values i.e. the range argument.
PAID and NOT PAID are the criteria in which the
sum is based i.e. the criteria argument.
C3:c7 is the range of cells that contain the value
you want add based on the specified criteria i.e. the sm_range argument.
Your worksheet should now look like the one in the figure
below.
CONCATENATE FUNCTION WITH ILLUSTRATION
This is an Excel built-in function used to join two or more
text items together.
The syntax for the CONCATENATE function looks like this: =CONCATENATE(TEXT1,TEXT2,TEXT3) press
Enter.
The function takes up to 255 text items. A comma is used to
separate each text item. If you actually a comma or a space or any special
character, you need to treat it as a text. If the item is a text, you enclose
it in double quote. A comma, space or any special character is treated as a text.
If the item is a number, or a formula, you don’t need to
enclose it in a double quote.
For example, the output of the CONCATENATE function:
=CONCATENATE(“Keyboard”,”=”,2,”
“,“Mouse”,”=”,3,” “,“Monitor”,”=”,4) is:
Keyboard=2 Mouse=3
Monitor=4
This is the end of the part 3 of chapter 2.
Now go over to Part 4: HOW TO CALCULATE YOUR CGPA USING MS-EXCEL
Was this tutorial helpful to you?
Inform your friends about this post by clicking the share button below.
Comment below if you are hooked up along the installation process.
Also click Here to subscribe for free so that you will get our latest game updates in your email.
Recommended Entrepreneurship Books
The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.
How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.
How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.
Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.
How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.
Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.
How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!