SUM all Instances Q

  • Thread starter Thread starter Seanie
  • Start date Start date
S

Seanie

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.
 
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.
 
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.
 
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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
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 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))
 
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.
 
I could have used:
=a1+b1
will fail if either contains non-numeric data (just to be consistent with the
=sum()).
 
Back
Top