Mutliple Sumproduct criteria

[PJFry]

I have three columns, Vendor, Price and Quantity on a data tab. On a summary
tab I have all of my vendors. How can I do a sumproduct (price and quantity)
by vendor on my summary tab?

I have been sorting my data by vendor and manually selecting the sumproduct
range, but as we add vendors, this will be come unmanageable.

Thanks!

 

[smartin]

I have three columns, Vendor, Price and Quantity on a data tab. On a summary
tab I have all of my vendors. How can I do a sumproduct (price and quantity)
by vendor on my summary tab?

I have been sorting my data by vendor and manually selecting the sumproduct
range, but as we add vendors, this will be come unmanageable.

Thanks!

No need to sort. Try something like this on your Summary worksheet @ B2,
where the vendor ID is in A2, and fill down:

=SUMPRODUCT((Data!$A$2:$A$11=Summary!$A2)*(Data!$B$2:$B$11)*(Data!$C$2:$C$11))

Alternative syntax:

=SUMPRODUCT(--(Data!$A$2:$A$11=Summary!$A2),(Data!$B$2:$B$11),(Data!$C$2:$C$11))