Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • How to Setup Gmail in Windows 10 Windows 10
  • How to Use the Windows 10 Camera App Windows 10
  • How To Deactivate Facebook Messenger How-To
  • 10 Tips to Speed Up Slow Windows 10 PC How-To
  • How To Track Someone Down Using Online Sources Web Site Tips
  • How to Create Live Wallpapers on Samsung Phones How-To
  • Best calculator for college 2020 Calculator
  • How to Enable or Disable Startup Programs on macOS macOS

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Posted on October 6, 2020 By blog_786 No Comments on How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

The three most commonly used formulas in Excel for doing simple math calculations are COUNT, SUM, and AVERAGE. Whether you’re managing your financial budget in Excel, or just keeping an eye on your next vacation, you’ve probably used one of these functions before.

In this article, we’ll go over the basics of these three functions and their respective and useful counterparts: COUNTIFS, SUMIFS, and AVERAGE.

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Let’s say we are starting a new online mobile phone business and we have a spreadsheet that lists the sales we made in the first two months. Download a sample Excel spreadsheet here .

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Excel COUNT, SUM, and AVERAGE

To find out how many mobile phones we have sold we can quickly use the COUNT formula as below:

= COUNT (E2: E16)

Alternatively, to get the total amount of sales we have made, we can use the SUM formula as shown below:

= SUM (E2: E16)

Finally, to find out the average sales we made for all phones, we can use the AVERAGE formula as shown below:

= AVERAGE (E2: E16)

The result should be like this:

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

The COUNT, SUM, and AVERAGE formulas will only work for records where the cell value is in numeric format. Any entry in the formula range (for example, E2: E16 in this example) that is not numeric will be ignored.

So make sure that all cells in the COUNT, SUM, and AVERAGE formula are formatted as numbers and not as text. Try using the same formula, but with a range of E: E instead of E2: E16. It will return the same result as before, because it ignores the title (i.e. Sales Price), which is in text format.

But what if we want to know the number of sales, total sales and average sales per phone only for those sold in the US? COUNTIFS, SUMIFS and AVERAGEIF play an important role here. Pay attention to the formula below:

COUNTIFS

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Formula breakdown:

  1. = COUNTIFS (- “=” denotes the beginning of a formula in a cell, and COUNTIFS is the first part of the Excel function we use.
  2. D2: D16 – refers to the data range to check if it meets the criteria for inclusion in the counting formula.
  3. “USA” – search criteria in the specified data range (D2: D16)

The formula returns 6, the number of sales of items shipped from the US warehouse.

SUMIFS

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Formula breakdown:

  1. = SUMIF (- “=” again indicates the beginning of the formula.
  2. E2: E16 – Refers to the range of data that we would like total, i.e. i.e. the sales price in our example.
  3. D2: D16 – refers to the data range to check if it meets the criteria for inclusion in the total.
  4. “USA” – search criteria in the specified data range (D2: D16)

The formula shows the total sales of items shipped from the US warehouse to $ 6,050.

AVERAGEIFS

How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Formula breakdown:

  1. = AVERAGEIFS (- “=” indicates the beginning of the formula.
  2. E2: E16 – Refers to the range of data that we would like to average. In this In this example, we want to get the average sales for all phones sold in the US.
  3. D2: D16 – refers to a data range to test if it meets the criteria that should be included in the average formula.
  4. US is the criteria for searching in the specified data range.

The formula shows that in the US we sold this product for about $ 1008 per phone.

All three formulas can accept more than one criterion. For example, if we want to find out the same numbers (i.e., COUNT, SUM and AVERAGE) for products sold in the US, but specifically for the Samsung brand only, we just need to add a data range to validate and then its criteria.

See the example below where the second criterion is added to the initial criteria checks. (Blue text indicates the first criterion, and red indicates the second criterion)

= COUNTIFS ( D2: D16, “USA” , < span style = "color: # ff0000;"> B2: B16, “Samsung” E2: E16, D2: D16, “USA” , B2: B16, “Samsung” E2: E16, D2: D16 , “USA” , B2: B16, “Samsung”

You will notice that Excel also has the formulas COUNTIF, SUMIF, and AVERAGE without the “S” suffix. They are used in the same way as COUNTIFS, SUMIFS, and AVERAGEIF. However, those who do not have the “S” suffix in their formula can only use one criterion for each formula.

Since the syntax is slightly different, I would recommend using COUNTIFS, SUMIFS, and AVERAGEIFS only, since it can be used for one or multiple criteria if needed. Enjoy!

–

Share this:

  • Twitter
  • Facebook
MS Office Tips

Post navigation

Previous Post: 5 Power User Features in Chrome Everyone Should Know
Next Post: Ring vs. Nest vs. Arlo: Which Smart Doorbell Should You Buy?

Related Posts

  • Add Headers and Footers to a PowerPoint Presentation MS Office Tips
  • Compare Two Excel Files using SpreadSheet Compare MS Office Tips
  • Polling in microsoft teams meeting MS Office Tips
  • The best keyboard shortcuts required for Microsoft Word for better progress MS Office Tips
  • Turn Caps Lock Text back to Normal in MS Word MS Office Tips
  • How to Open a WPS File 2020 MS Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Archives

  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • Protect Your Facebook Account From Hackers (2017)
  • 4 Ways to Share WiFi Without Giving Away Your Password
  • How to Get 2 Free Audiobooks From Audible Trial Instead of 1
  • 10 Things to Check Before Publishing a YouTube Video
  • A Simple Trick to Get 50% Discount on Audible for Three Months
DMCA.com Protection Status

Recent Posts

  • Here are the Top 10 highest paid YouTubers of 2017
  • Protect Your Facebook Account From Hackers (2017)
  • 4 Ways to Share WiFi Without Giving Away Your Password
  • How to Get 2 Free Audiobooks From Audible Trial Instead of 1
  • 10 Things to Check Before Publishing a YouTube Video

Recent Comments

  1. This Simple Trick Lets You Play YouTube in the Background on iOS on YouTube Not Working? Here Are Quick Fixes To Try
  2. How to Bypass Chromecast DNS and Circumvent Geo Blocking on 5 Cool Websites to Find Good Movies and TV Shows on Netflix
  3. 5 Cool Websites to Find Good Movies and TV Shows on Netflix on check netflix video quality internet explorer
  4. SmartDNS vs VPN “What’s the Difference?” on How to Watch Apple TV on Roku
  5. How to go Back to the old YouTube Layout (2013) on 10 Things to Check Before Publishing a YouTube Video
  • How to Remove Location and EXIF data from your Photos How-To
  • The Definitive Guide to Battery Charging Featured Posts
  • how do i set the print area in google sheets Computer Tips
  • How To Get Your Own Skype Phone Number Computer Tips
  • How to Transfer WhatsApp Chat History from iPhone to Android iPhone
  • The Kindle Desktop App: Is It Any Good? Free Software Downloads
  • Best ti30xiis scientific calculator 2020

    Best ti30xiis scientific calculator 2020. 1 - 1 X 10-Digit Scientific Calculator Top Reviews Works as expected at a low

  • What Is HDR On An iPhone Camera? iPhone

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version