Solved

Excel SumProduct with Find Across Multiple Rows and Columns

Posted on 2014-10-30
9
145 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 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