How to use the SUMPRODUCT function in Excel to count and add by ranges

The easy handling of Excel spreadsheets makes it the most popular software of its kind on the market. Its immense amount of features makes it preferred by large and small companies.

Excel is the most widespread ally of accountants for calculating and registering sales. It is also of daily use for operations as simple as addition and subtraction when we want to keep our daily accounts of income and expenses organized. By downloading Excel from its website  you will have an excellent assistant to perform financial calculations, registration and statistical analysis.

Index(  )

  1. What formulas do I need to count and sum by ranges in Excel cells?
    1. With the formula ‘=SUMPRODUCT(A:A,B:B)’
    2. With the formula =SUMPRODUCT(((Table1[Date])+(Table2[Month]))*(Table1[Agent]=B8)
  2. How do you know if the formulas you use with the SUMPRODUCT function turned out right?
    1. From Excel on mobile
    2. With Excel on computer

What formulas do I need to count and sum by ranges in Excel cells?

There are several formulas for counting and adding in Excel. This program can work as a simple calculator. However, when the set of numbers is large , it is recommended to use the “SUM” formula. For example, typing “=SUM(A2:A6)” will have a lower chance of error if you add cell by cell “=A2+A3+A4+A5+A6”. To create this formula follow these steps:

  1. Type “=SUM” in a cell followed by an opening parenthesis.
  2. Add A2:A4 which is the first range or argument of the formula.
  3. Separate the following range with a comma.
  4. Drag the second argument to select the cells.
  5. End with a closing parenthesis and press “enter”.

With the formula ‘=SUMPRODUCT(A:A,B:B)’

This formula is commonly used when it is required to quickly count the number of records within a database that correspond to a certain period, such as the record count per month that is stipulated to count or add cells in Excel . This function basically takes arrays of values, multiplies the elements together, and then adds those products to output a result.

With the formula =SUMPRODUCT(((Table1[Date])+(Table2[Month]))*(Table1[Agent]=B8)

This formula has the function of returning the net values ​​of sales made by an agent where the total values ​​of expenses made by that agent are determined. For this you should use a standard Excel table with structured references instead of ranges. Here the data of the ranges of sales, expenses and agents specified with names. The formula returns the sum of all sales and expenses for the agent listed in cell B8.

How do you know if the formulas you use with the SUMPRODUCT function turned out right?

When there is a problem with the formulas , Excel issues an error message . When this happens it is because Excel cannot understand what you are trying to do. In this chao it is best to start over. The easiest way is to select accept in the message box or press “esc”. Excel will take you to the erroneous formula which will be in edit mode and the part containing the problem will be highlighted.

If you don’t get the reason for the error, press ‘esc’ again or cancel in the formula bar to exit edit mode. You can use a checklist that leads you to the possible cause such as errors adding negative values ​​in Excel , formula linking errors, numbers without formatting, incorrect argument syntax and others.

From Excel on mobile

Excel is available on Android and iOS mobile devices. However, the functionality of the program will depend on the operating system of your cell phone. In addition to an application compatible with Excel, we recommend using your email to share your files . However, taking advantage of online storage services can be a solution.

If your phone has Windows installed, Microsoft Office Mobile will allow you to view and edit spreadsheets and create new Excel documents. If your mobile doesn’t support this application, you can download Documents To Go , which is a fairly well-known Excel viewer and editor. You can also opt for QuickOffice Connect Mobile Suite which has Android and Apple compatible versions.

However, some limitations will arise, since its complexity does not allow full access to its functions. Things like adding or counting cells of the same color and other high level actions will be limited by the memory or power of your device.

With Excel on computer

Your PC is the best device for running Excel. First, it is very important that you have an original license from Microsoft so that you can download this program for free. This will guarantee its optimal performance. You will have access to all the available updates as well as information about the news that its new versions offer.

Even more, Excel offers you the online version that you don’t need to download on your PC and take up space that is necessary for other of your applications. By using Excel online you have access to all the features of its original version. In addition, you can take advantage of cloud storage and be able to collaborate in real time with other users in search of suggestions and recommendations for managing your spreadsheet.


by Abdullah Sam
I’m a teacher, researcher and writer. I write about study subjects to improve the learning of college and university students. I write top Quality study notes Mostly, Tech, Games, Education, And Solutions/Tips and Tricks. I am a person who helps students to acquire knowledge, competence or virtue.

Leave a Comment