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.

=SUMPRODUCT(('HC By Branch Excluding Cz CW'!\$A:\$A=Sheet1!\$B\$28)*('HC By Branch Excluding Cz CW'!\$B:\$B=Sheet1!\$A29))
LVL 1
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Director, 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
Author 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
Director, 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
Author Commented:
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
0
Director, 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
Mechanical EngineerCommented:
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
Mechanical EngineerCommented:
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
Mechanical EngineerCommented:
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

Experts Exchange Solution brought to you by