Solved

Compare multiple ranges using sumproduct

Posted on 2011-03-02
3
218 Views
Last Modified: 2012-05-11
I am trying to use SUMPRODUCT in Excel to compare mulitple ranges but cannot get the calculation to evaulate successfully.

Firstly I want a column to be within a specific date range (this I have no problem with) then the other column I am evaluating I want to be one of a list of values (this is causing me trouble)

I attach a example of the code which I have unsuccessfully tried.

Columns AW and L are date ranges and B is populated by the multiple value range.

I appreciate that I could create a seperate SUMPRODUCT statement for each value I wish to evaluate but I hoped that there was a more efficient way of calculating this.
=SUMPRODUCT(([data.xls]PM!$AW$2:$AW$65000>=L3)*([data.xls]PM!$AW$2:$AW$65000<L4)*([data.xls]PM!$B$2:$B$65000="Assigned" + [data.xls]PM!$B$2:$B$65000="Assigned To Vendor"))

Open in new window

0
Comment
Question by:JayceW
[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
  • 2
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 430 total points
ID: 35016757
Try:
=SUMPRODUCT(([data.xls]PM!$AW$2:$AW$65000>=L3)*([data.xls]PM!$AW$2:$AW$65000<L4)*(([data.xls]PM!$B$2:$B$65000="Assigned")+([data.xls]PM!$B$2:$B$65000="Assigned To Vendor")))
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 430 total points
ID: 35016764
Or:
=SUMPRODUCT(([data.xls]PM!$AW$2:$AW$65000>=L3)*([data.xls]PM!$AW$2:$AW$65000<L4)*([data.xls]PM!$B$2:$B$65000={"Assigned","Assigned To Vendor"}))
0
 
LVL 9

Assisted Solution

by:McOz
McOz earned 70 total points
ID: 35016831
If you have your list of values in a range somewhere, you could use something like this (where "YourListRange" is a valid reference to your list):
=SUMPRODUCT(($AW$2:$AW$65000>=L3)*($AW$2:$AW$65000<L4)*(IsError(Match($B$2:$B$65000,YourListRange,0))=FALSE))

Open in new window

0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 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