How could I achieve the following via formula

I want to add up all the instances that certain numbers appear in Col
B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20
or 26 or 30 or 55 appears in Row1 then add the value that appears in
Row1 COL D and do this for every row in sheet where a values exist in
COL B.

I know how I could do it if I was looking for only 1 instance in ColB,
but the multi instances has me guessing

Thanks

You want Sumif, as in:
=sumif(B:B,1,D:D)

Regards,
Fred

"Seanie" wrote in message
...
How could I achieve the following via formula

I want to add up all the instances that certain numbers appear in Col
B with their corresponding values in COL D e.g. if 1 or 3 or 6 or 20
or 26 or 30 or 55 appears in Row1 then add the value that appears in
Row1 COL D and do this for every row in sheet where a values exist in
COL B.

I know how I could do it if I was looking for only 1 instance in ColB,
but the multi instances has me guessing

Thanks

Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria

If the ,1,2,3,4,5 part means sum them if value in B equals any one of those 5
values, then simply write a longish formula for all cases:

=SUMIF(B:B,1,D:D)+SUMIF(B:B,2,D:D)+SUMIF(B:B,3,D:D )+SUMIF(B:B,4,D:D)+SUMIF(B:B,5,D:D)

You only have to do it once.

"Seanie" wrote:

Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria


.

Is there any limit to the 'longish' formula? Something tells me 8 is
the limit

Not in this case. The limit is the number of characters that can make up a
formula. That is 1024 in Excel 2003.

You are thinking of nested formulas when you mention 8. It's actually 7
levels of nesting, which gives you 8 options.

"Seanie" wrote:

Is there any limit to the 'longish' formula? Something tells me 8 is
the limit

.

Another:

=SUM(SUMIF(B:B,{1,2,3,4,5},D:D))



Seanie wrote:

Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria

--

Dave Peterson

Dadgummit! I almost went there. Stopped before adding the outside SUM(), it
didn't work as an array formula, so I went with the individual SUMIF()s.

Hope OP sees your post.

"Dave Peterson" wrote:

Another:

=SUM(SUMIF(B:B,{1,2,3,4,5},D:D))



Seanie wrote:

Thanks, that would be ideal if I could do =SUMIF(B:B,1,2,3,4,5,D:D),
but it only takes 1 criteria

--

Dave Peterson
.

Thanks guys

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

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 he
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

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

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

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))

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

I could have used:
=a1+b1
will fail if either contains non-numeric data (just to be consistent with the
=sum()).

Dave Peterson wrote:

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

--

Dave Peterson