Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel SumProduct with Find Across Multiple Rows and Columns

Posted on 2014-10-30
Medium Priority
171 Views
Hello, I'm trying to do a sumproduct across multiple rows and columns.  I've figured out the multiple row aspect but I'm having trouble trying to see if row 7 has 2007 in it to add up the columns.

=SUMPRODUCT(('HC By Branch Excluding Cz CW'!\$A:\$A=Sheet1!\$B\$28)*('HC By Branch Excluding Cz CW'!\$B:\$B=Sheet1!\$A29))
0
Question by:mattfmiller
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 3

LVL 24

Expert Comment

ID: 40413491
Could you please clarify, as whilst I understand the formula, I don't understand your question.

Maybe some sample data would help.
0

LVL 1

Author Comment

ID: 40413518
2007 1H      2007 2H
Column A      Column B
X                            Y                                      100      200
X                            X                                      300      400

IF (X,Y,2007) Then 300

IF(X,X, 2007) Then 700

IF (X,Y, 2008) Then 0
0

LVL 24

Expert Comment

ID: 40413642
Not much clearer, I'm afraid. I don't understand what 2007 1H, 2007 2H are, what the values in columns C and D (presumably) are, and how a row would be 2008.
0

LVL 1

Author Comment

ID: 40413699
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
0

LVL 24

Expert Comment

ID: 40413728
And what does X and Y have to do with anything. And where is 2008?

It would help if you could demo it in a spreadsheet, with sample data and the sample output you want.
0

LVL 81

Expert Comment

ID: 40413858
Assuming that the data to be added lies in columns C:F, you might use:
=SUMPRODUCT(('HC By Branch Excluding Cz CW'!\$A:\$A=Sheet1!\$B\$28)*('HC By Branch Excluding Cz CW'!\$B:\$B=Sheet1!\$A29)*(LEFT('HC By Branch Excluding Cz CW'!\$C\$7:\$F\$7,4)="2007"),'HC By Branch Excluding Cz CW'!\$C:\$F)

There are several things to note in the above formula:
1. The columns being summed should be separated by a comma rather than multiplied using *. This allows SUMPRODUCT to ignore text values in those columns.
2. The year being matched should be surrounded by doublequotes as you are using LEFT function to separate the year from 1H or 2H. If you want to use a cell value containing a number for the year, then you might concatenate an empty string to convert the number into text:
=SUMPRODUCT(('HC By Branch Excluding Cz CW'!\$A:\$A=Sheet1!\$B\$28)*('HC By Branch Excluding Cz CW'!\$B:\$B=Sheet1!\$A29)*(LEFT('HC By Branch Excluding Cz CW'!\$C\$7:\$F\$7,4)=(Sheet1!\$A\$15 & "")),'HC By Branch Excluding Cz CW'!\$C:\$F)

SUMPRODUCT is going to be awfully slow looking at worksheets with over a million rows of data. The SUMIFS function is a lot faster (because it only looks at populated rows of data), but you'll need to modify how the columns are captioned with the years. If you posted a sample workbook, we could offer a more concrete suggestion.
0

LVL 81

Expert Comment

ID: 40413886
In my test workbook, the SUMIFS formula looked like this:
=SUMIFS(OFFSET(A:A,0,MATCH("2007*",A1:F1,0)-1),A:A,"X",B:B,"Y")+SUMIFS(OFFSET(A:A,0,MATCH("2007*",A1:F1,0)),A:A,"X",B:B,"Y")
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 40413897
Or even simpler SUMIFS using an array constant:
=SUM(SUMIFS(OFFSET(A:A,0,MATCH("2007*",A1:F1,0)-{1,0}),A:A,"X",B:B,"Y"))
0

LVL 1

Author Closing Comment

ID: 40441255
Thank you, this worked for me.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month4 days, 19 hours left to enroll