How to "lookup" with multiple criteria.

[drewm]

Hello All,

I am stumped!

It seems like such an easy thing, I just want to find the value of of the product sold to a specific account.

Ie if customer X purchased product Y this month, the put the value for the months sales into the appropriate cell..

I have searched Google & Chandoo and have tried various combinations of sumifs, sumproduct, vlookup, index & match, array formulas and others.

Most of the time I am just gettings N/A & #N/A, sometimes it seems that I have managed to get it to work, but when I double check the result is incorrect.

I assume that there is something that I am not "getting".

Any help would be greatly appreciated.

Thanks,

Drew

 

[Nebu]

Hi:

Please find the attached.

Thanks

 

[Khalid NGO]

Hi Drew,
Welcome to the forum Chandoo.org

Try this in B5:
=SUMPRODUCT(($O$6:$O$34=$A5)*($P$6:$P$34=B$4)*($R$6:$R$34))
Copy down and across.

Regards,

 

[Syedali]

PFA, Using pivot also.

 

[drewm]

Hello Nebu, Khalid & Syed,

Thank you all for your responses.

All of them worked and after spending some time studying your answers, I am delighted to have learnt several new and helpful techniques to use in my work.

Thank you again.

Drew

 

[Khalid NGO]

You are welcome Drew.
Thanks for the valuable feedback.