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