Mode Function with 2 Criteria

B

bbrowers

I have a worksheet that has part number and customer name and the pric
paid on each invoice for the last 4 years. One customer may have pai
multiple prices in the last 4 years. I would like to create a formul
to calculate the mode of the price but only if the vendor and par
number are the same. I have tried usin
{=if(vendersheet1=vendorsheet2,if(partnumbersheet1=partnumbersheet2,mode(pricesheet2),0),0)}
But I just get zero for an answer. I have also tried mode with a matc
statement inside but I only get the mode for the whole worksheet. An
help would be greatly appreciated!

:confused
 
D

Domenic

Try the following array formula that needs to be confirmed wit
CONTROL+SHIFT+ENTER, not just ENTER...

=MODE(IF((VendorRange="Vendor")*(PartNumRange="PartNum"),PriceRange))

If parts numbers are numerical values and not text, remove the quotes.

Hope this helps!
 
Top