I'm guessing that you have some non-numeric entry in column D.

=sum(a1:b1)
will ignore text entries

But using multiplication
=a1*b1
will result in a #value! error.

Seanie wrote:

I tried below, which should just return all "Londons" Sales of 18,20,
32 etc etc, but I get a #Value

=SUM(SUMPRODUCT(('Sales Mix'!A:A="London")*('Sales Mix'!
B:B={18,20,32,140,172,208,212,500,99,204,203})*'Sa les Mix'!D:D))

This formula below gives me the correct total sales I'm after in all
Areas

=SUM(SUMIF('Sales Mix'!B:B,
{18,20,32,140,172,208,212,500,99,204,203},'Sales Mix'!D:D))

--

Dave Peterson