Solved

Excel SumProduct with Find Across Multiple Rows and Columns

Posted on 2014-10-30
9
123 Views
Last Modified: 2014-11-13
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.

Thank you for your help.

=SUMPRODUCT(('HC By Branch Excluding Cz CW'!$A:$A=Sheet1!$B$28)*('HC By Branch Excluding Cz CW'!$B:$B=Sheet1!$A29))
0
Comment
Question by:mattfmiller
  • 3
  • 3
  • 3
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

by:mattfmiller
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

by:Phillip Burton
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

by:mattfmiller
ID: 40413699
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:Phillip Burton
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 80

Expert Comment

by:byundt
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 80

Expert Comment

by:byundt
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 80

Accepted Solution

by:
byundt earned 500 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

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now