Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Compare multiple ranges using sumproduct

Posted on 2011-03-02
3
Medium Priority
?
234 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
  • 2
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1720 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 1720 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 280 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

926 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