Weighted Average with Two Lookups

Hello,

I'm trying to calculate a weighted average based on one criteria and have been successful thanks to this forum by using an array formula. I'm now trying to add in a second criteria and cannot seem to make it work. Below is the original formula where B5 is the one criteria:

{=SUM(IF(Detail!$G$2:$G$500=B5,Detail!$C$2:$C$500*Detail!$H$2:$H$500)/SUMIF(Detail!$G$2:$G$500,B5,Detail!$C$2:$C$500))}

Essentially, I'm trying to take the balance (Column C) x Rating (Column H) / Total Balance (Column C) using 2 criteria (Cells B5 & R5). I've tried the formula below and believe the "AND" part is messing it up. I'm not too familiar with arrays so maybe it can't be done this way. Below is what I've tried:

{=SUM(IF(AND(Detail!$G$2:$G$500=B5,Detail!$Q$2:$Q$500='Risk Rating'!R5),Detail!$C$2:$C$500*Detail!$H$2:$H$500)/SUMIFS(Detail!$C$2:$C$500,Detail!$G$2:$G$500,'Risk Rating'!B5,Detail!$Q$2:$Q$500,'Risk Rating'!R5))}

Any help would be appreciated.

Thanks!

That worked great. Thanks Bernie for the quick turnaround!

Ok - I have one more question regarding this. I plan on creating a combo box that will allow the user to select the criteria in cell R5. Let's say for example, the criteria are North, South, East, and West. The user can drop down on one of those and filter by that and by the criteria in cell B5. I would also like to add an "All" criteria - where it just does a weighted average based on cell B5 only. So essentially the formula would look like: If B5 = x and R5 = y, then do the average, but if B5 = x and R5 = "All" then average the entire table based on B5. I modified the formula two posts back to add an "If Error" because when the criteria in B5 wasn't available, I was getting the #DIV/0 error.

Thanks

Once again - Thank you very much!! This is incredibly helpful.

Noted. I was unaware, but will follow this in the future.

Thanks