Compare multiple ranges using sumproduct

Posted on 2011-03-02
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

Question by:JayceW
  • 2
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 430 total points
ID: 35016757
=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")))
LVL 85

Accepted Solution

Rory Archibald earned 430 total points
ID: 35016764
=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"}))

Assisted Solution

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):

Open in new window


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula to split product code 5 16
Turn several entries on single cell, into individual lines 14 27
And OR formula 5 22
Assigning VBA code to a specific worksheet 3 24
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

773 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