=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),(Data1!$E$1:$E$500))

Hi

=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),
(Data1!$E$1:$E$500))

In plain english the formula above means: Sum cells E1 to E500  (last
argument) if in B1 to B500 the value is "March" and if in C1 to C500
the value is "New York".

SUMPRODUCT is like SUMIF on steroids and it allows you to automate all
these reports that you develop with data that you import from a large
centralized database, centralized accounting, manufacturing and sales
programs or the Internet.

The last argument (preceded by a comma) tells Excel what to sum and
the two previous arguments (separated by an asterisk) tell Excel about
the conditions for summing.

You can do this with SUMPRODUCT and much more.

Within a table where months are in column "A" and cities in row "11"
the formula would read like this

=SUMPRODUCT((Data1!$B$1:$B$500=$A13)*(Data1!$C$1:$C$500=B$11),(Data1!$E
$1:$E$500))

Visit: http://www.excel-examples.com/00-excel-function-sumproduct.htm

Rediscover Excel and enjoy
0
pleclerc (6)
10/19/2009 2:50:33 PM
excel 39879 articles. 2 followers. Follow

3 Replies
960 Views

Similar Articles

[PageSpeed] 10

Tell us something new, we have been doing this for years.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed 
explanation.

-- 
__________________________________
HTH

Bob

"vbaexcel" <pleclerc@excel-vba.com> wrote in message 
news:3f431e11-0464-4b56-9c35-e1a33147dc06@l31g2000vbp.googlegroups.com...
> Hi
>
> =SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),
> (Data1!$E$1:$E$500))
>
> In plain english the formula above means: Sum cells E1 to E500  (last
> argument) if in B1 to B500 the value is "March" and if in C1 to C500
> the value is "New York".
>
> SUMPRODUCT is like SUMIF on steroids and it allows you to automate all
> these reports that you develop with data that you import from a large
> centralized database, centralized accounting, manufacturing and sales
> programs or the Internet.
>
> The last argument (preceded by a comma) tells Excel what to sum and
> the two previous arguments (separated by an asterisk) tell Excel about
> the conditions for summing.
>
> You can do this with SUMPRODUCT and much more.
>
> Within a table where months are in column "A" and cities in row "11"
> the formula would read like this
>
> =SUMPRODUCT((Data1!$B$1:$B$500=$A13)*(Data1!$C$1:$C$500=B$11),(Data1!$E
> $1:$E$500))
>
> Visit: http://www.excel-examples.com/00-excel-function-sumproduct.htm
>
> Rediscover Excel and enjoy 


0
BobNGs (423)
10/20/2009 8:11:05 AM
On Oct 20, 4:11=A0am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Tell us something new, we have been doing this for years.
>
> Seehttp://www.xldynamic.com/source/xld.SUMPRODUCT.htmlfor a detailed
> explanation.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "vbaexcel" <plecl...@excel-vba.com> wrote in message
>
> news:3f431e11-0464-4b56-9c35-e1a33147dc06@l31g2000vbp.googlegroups.com...
>
> > Hi
>
> > =3DSUMPRODUCT((Data1!$B$1:$B$500=3D"March")*(Data1!$C$1:$C$500=3D"New Y=
ork"),
> > (Data1!$E$1:$E$500))
>
> > In plain english the formula above means: Sum cells E1 to E500 =A0(last
> > argument) if in B1 to B500 the value is "March" and if in C1 to C500
> > the value is "New York".
>
> > SUMPRODUCT is like SUMIF on steroids and it allows you to automate all
> > these reports that you develop with data that you import from a large
> > centralized database, centralized accounting, manufacturing and sales
> > programs or the Internet.
>
> > The last argument (preceded by a comma) tells Excel what to sum and
> > the two previous arguments (separated by an asterisk) tell Excel about
> > the conditions for summing.
>
> > You can do this with SUMPRODUCT and much more.
>
> > Within a table where months are in column "A" and cities in row "11"
> > the formula would read like this
>
> > =3DSUMPRODUCT((Data1!$B$1:$B$500=3D$A13)*(Data1!$C$1:$C$500=3DB$11),(Da=
ta1!$E
> > $1:$E$500))
>
> > Visit:http://www.excel-examples.com/00-excel-function-sumproduct.htm
>
> > Rediscover Excel and enjoy

Hi, Bob

It is not new to you, me and only 5% of Excel users.

If more users knew about SUMPRODUCT Excel would be the top reporting
application on the market and users would not have to wait for IT guys
to develop their report.

In your example you are using an asterisk before the last argument. If
the first cell of your dataset is a title or if a cell in your range
contains text you will get an error message (#Value). By using a comma
you correct this problem.

So it is not because YOU know about something that others do. Share
your old tips you will help a lot of people.
0
pleclerc (6)
10/20/2009 9:20:25 AM
> "vbaexcel" <pleclerc@excel-vba.com> wrote in message 
> news:de58963a-a667-42d3-8e04-807ab7b826ba@o21g2000vbl.googlegroups.com...>
>
> Hi, Bob
>
> It is not new to you, me and only 5% of Excel users.
>
> If more users knew about SUMPRODUCT Excel would be the top reporting
> application on the market and users would not have to wait for IT guys
> to develop their report.


That may be so, but those that need such functionality come to these NGs and 
other forums and they get a response, usually suggesting SP. There are 
MILLIONS of SP answers out there, so it seems those that need to know either 
know or know how to find out.


> In your example you are using an asterisk before the last argument. If
> the first cell of your dataset is a title or if a cell in your range
> contains text you will get an error message (#Value). By using a comma
> you correct this problem.


As I have many examples, I have no idea what you are referring to, but I am 
sure you are taking it out of context. Sometimes it is correct to use -- and 
comma, sometimes you have to use *, it all depends upon the context.


> So it is not because YOU know about something that others do. Share
> your old tips you will help a lot of people.


That is exactly what that page and the archives do. 


0
BobNGs (423)
10/20/2009 10:22:07 AM
Reply:

Similar Artilces:

=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),(Data1!$E$1:$E$500))
Hi =SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"), (Data1!$E$1:$E$500)) In plain english the formula above means: Sum cells E1 to E500 (last argument) if in B1 to B500 the value is "March" and if in C1 to C500 the value is "New York". SUMPRODUCT is like SUMIF on steroids and it allows you to automate all these reports that you develop with data that you import from a large centralized database, centralized accounting, manufacturing and sales programs or the Internet. The last argument (preceded by a comma) tells Excel what to...