Solved

Excel SumProduct with Find Across Multiple Rows and Columns

Posted on 2014-10-30
9
129 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
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
 
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 81

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 81

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 81

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL 2013 question. 4 28
Auto Populate Day Month  2 digit Date 4 19
Excel formula Sumif not working 4 28
vba autofilter in row 4 6 11
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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