ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   SUM all Instances Q (https://www.excelbanter.com/excel-worksheet-functions/258120-sum-all-instances-q.html)

Seanie

SUM all Instances Q
 
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

Fred Smith[_4_]

SUM all Instances Q
 
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



Seanie

SUM all Instances Q
 
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



JLatham

SUM all Instances Q
 
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


.


Seanie

SUM all Instances Q
 
Is there any limit to the 'longish' formula? Something tells me 8 is
the limit


JLatham

SUM all Instances Q
 
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

.


Dave Peterson

SUM all Instances Q
 
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

JLatham

SUM all Instances Q
 
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
.


Seanie

SUM all Instances Q
 
Thanks guys


Seanie

SUM all Instances Q
 
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

SUM all Instances Q
 
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

Seanie

SUM all Instances Q
 
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

SUM all Instances Q
 
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

Seanie

SUM all Instances Q
 
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

SUM all Instances Q
 
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

SUM all Instances Q
 
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


All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com