Prev: Autofit height
Next: VLook-Up Possibly Conditional Formatting
From: Dave Peterson on 6 Mar 2010 11:01
Are you using xl2007?

If you are, you may want to try =sum(sumifs(...))
(I didn't load xl2007 to try that.)

But this seemed to work for me in xl2003:

=SUM(SUMPRODUCT((A1:A10="London")*(B1:B10={1,2,3,4,5})*D1:D10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Seanie wrote:
>
> A further twist on =SUM(SUMIF(B:B,{1,2,3,4,5},D:D))
>
> How could I incorporate a further criteria eg. If ColA contains
> "London" and if ColB contains one of 1,2,3,4,5 then add up all those
> instance in ColD

--

Dave Peterson
From: Seanie on 6 Mar 2010 14:40
Yes Dave, using 2007. Would replacing 1,2,3,4,5 etc with a Range Name
where all my values listed work? - just can't test this as this moment


From: Dave Peterson on 6 Mar 2010 15:19
I don't have xl2007 on this pc.

But post back with your results <bg>.

Seanie wrote:
>
> Yes Dave, using 2007. Would replacing 1,2,3,4,5 etc with a Range Name
> where all my values listed work? - just can't test this as this moment

--

Dave Peterson
From: Seanie on 7 Mar 2010 02:54
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})*'Sales 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))

From: Dave Peterson on 7 Mar 2010 08:02
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})*'Sales 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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Autofit height
Next: VLook-Up Possibly Conditional Formatting