summarize raw data with sumproduct in Excel

Q88. In Excel, how can I use SUMPRODUCT to summarize my raw data?

For example, I have my raw Sales Data information as shown below. In Excel, how can I get region wise Sales data?

excel summarize raw data with sumproduct

To do it in Excel, here is the answer:

a) Enter the formula =SUMPRODUCT(--($O$5:$O$14 = $O15),P$5:P$14) to get Q1-2016 Sales data for North Region.

The first argument "($O$5:$O$14 = $O15)" looks for all values in range O5 : O14 that are same as value specified in cell O15. The "--" converts TRUE/FALSE to 1 or 0.

In the first argument, notice it is mentioned as $O15 so that the formula could be copied across rows.

The second argument picks and sums up the data if the first argument is TRUE for a value. Notice that the column reference P does not have a preceding $ so that the formula could be copied across columns.

Drag the formula to get Sales data for the rest of the quarters / regions as shown below.

excel excel summarize raw data with sumproduct

 

You can find similar Excel Questions and Answer hereunder

1) Is there a way to easily shade alternate rows in a data range for improving readability?

2) How can I enter information in multiple cells simultaneously?

3) How can I filter and copy only filtered data using VBA?

4) How can I add interval to dates?

5) Filtering the value field in a pivot table in Excel

6) One of my formulas has returned an error - how can I trace the error to fix it?

7) How can I remove display of Gridlines in my worksheet using VBA?

8) How do I calculate monthly payment given Loan amount, Rate of Interest and period?

9) How can I change the meta-data of file (Author, Company Name and the like)?

10) How can I filter a table to get all records that have less than a particular value in a specific column?

11) How can I set the Source Data of charts using VBA?

12) Millions thousands custom number formatting in Excel

13) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

14) How can I list all files in a folder using VBA?

15) Remove the apostrophe cell text values in Excel