Business Applications Of Excel Sumif, Countif And Concatenate Functions

The SUMIF, COUNTIF and CONCATENATE functions are Excel’s built-in functions. These functions can also be applied practically to business systems.
Business Applications Of Excel Sumif, Countif And Concatenate Functions
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
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.
COUNTIF function – Table 2

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.
SUMIF function-table 1

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.
List of customers

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 entrepreneurs strategies book by buzzer joseph

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 book by buzzer joseph

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 by buzzer joseph

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.

diy grid and off-grid solar pv installation guide by buzzer joseph

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 cryptocurrency trading and investments by buzzer joseph

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 kindle book by buzzer joseph

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 kindle book by buzzer joseph and kelly joseph

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!