Matt Miller
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))
Thank you for your help.
=SUMPRODUCT(('HC By Branch Excluding Cz CW'!$A:$A=Sheet1!$B$28)*('
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
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.
ASKER
Sorry the 2007 1H was supposed to be over 100 & 300
2007 2H was supposed to be over 200 & 400
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.
It would help if you could demo it in a spreadsheet, with sample data and the sample output you want.
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)*(LE FT('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)*(LE FT('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.
=SUMPRODUCT(('HC By Branch Excluding Cz CW'!$A:$A=Sheet1!$B$28)*('
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)*('
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*",A 1:F1,0)-1) ,A:A,"X",B :B,"Y")+SU MIFS(OFFSE T(A:A,0,MA TCH("2007* ",A1:F1,0) ),A:A,"X", B:B,"Y")
=SUMIFS(OFFSET(A:A,0,MATCH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, this worked for me.
Maybe some sample data would help.