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))
LVL 1
mattfmillerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Could you please clarify, as whilst I understand the formula, I don't understand your question.

Maybe some sample data would help.
0
 
mattfmillerAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
mattfmillerAuthor Commented:
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
mattfmillerAuthor Commented:
Thank you, this worked for me.
0
All Courses

From novice to tech pro — start learning today.