Link to home
Start Free TrialLog in
Avatar of Matt Miller
Matt MillerFlag for United States of America

asked on

Excel SumProduct with Find Across Multiple Rows and Columns

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))
Avatar of Phillip Burton
Phillip Burton

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

Maybe some sample data would help.
Avatar of Matt Miller

ASKER

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
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.
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
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.
Avatar of byundt
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.
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")
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, this worked for me.