"p45cal" wrote in message
...

Peter T;488737 Wrote:
You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your
sample
data.

Regards,
Peter TYes, Peter, it gives the same result for his example. But further
testing yields some odd results; If the ranges in your formula are
expanded to cater for the full 15 rows possible, then when you:
Add a new item in table 1 after the last entry, call it 101 and give it
the value $3 in the second column of table 1,
then add 101 after the last entry in column 1 of table 2 and leave the
value empty..

I -*think *-the answer should be 748, but your formula gives 673.


Indeed you are right, I see why it's wrong but trickier to fix than I
thought!
Of course would be much easier if the data is tidied up a little beforehand.

Regards,
Peter T