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

Open in new window

JayceWAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rory ArchibaldConnect With a Mentor 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
 
Rory ArchibaldConnect With a Mentor 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
 
McOzConnect With a Mentor 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))

Open in new window

0
All Courses

From novice to tech pro — start learning today.