x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 239

# Compare multiple ranges using sumproduct

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"))
``````
0
JayceW
• 2
3 Solutions

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

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

Commented:
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))
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.