A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SumProduct not right



 
 
Thread Tools Display Modes
  #1  
Old January 8th, 2010, 10:49 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default SumProduct not right

This formula is summing ALL the incidences in the N column of what's in E2. I
want it to sum ONLY those that are in E2 but also also matching what's in A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve
  #2  
Old January 8th, 2010, 10:57 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_2_]
external usenet poster
 
Posts: 722
Default SumProduct not right

On Fri, 8 Jan 2010 13:49:01 -0800, Steve
wrote:

This formula is summing ALL the incidences in the N column of what's in E2. I
want it to sum ONLY those that are in E2 but also also matching what's in A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


It seems you have forgotten a comma. Try this:

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3),--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

Hope this helps / Lars-Åke

  #3  
Old January 9th, 2010, 09:29 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default SumProduct not right

=SUMPRODUCT((Data!$E$3:$E$5000=$A$3)*(Data!$R$3:$R $5000=E2)*(Data!$N$3:$N$5000))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
This formula is summing ALL the incidences in the N column of what's in
E2. I
want it to sum ONLY those that are in E2 but also also matching what's in
A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


  #4  
Old January 9th, 2010, 03:20 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default SumProduct not right

Nice eyes. That was it.

thanks,

Steve

"Lars-Ã…ke Aspelin" wrote:

On Fri, 8 Jan 2010 13:49:01 -0800, Steve
wrote:

This formula is summing ALL the incidences in the N column of what's in E2. I
want it to sum ONLY those that are in E2 but also also matching what's in A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


It seems you have forgotten a comma. Try this:

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3),--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

Hope this helps / Lars-Ã…ke

.

  #5  
Old January 9th, 2010, 03:23 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default SumProduct not right

Thank you. This works. The asterisks are also easier for me follow than than
the dashes. I think I'll use this instead.
Are those dashes always interchangable with the asterisks in those slightky
different positions ?

Thanks again,

Steve

"Ashish Mathur" wrote:

=SUMPRODUCT((Data!$E$3:$E$5000=$A$3)*(Data!$R$3:$R $5000=E2)*(Data!$N$3:$N$5000))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
This formula is summing ALL the incidences in the N column of what's in
E2. I
want it to sum ONLY those that are in E2 but also also matching what's in
A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


  #6  
Old January 10th, 2010, 02:49 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default SumProduct not right

Hi,

I always use the asterisks - it always works

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
news
Thank you. This works. The asterisks are also easier for me follow than
than
the dashes. I think I'll use this instead.
Are those dashes always interchangable with the asterisks in those
slightky
different positions ?

Thanks again,

Steve

"Ashish Mathur" wrote:

=SUMPRODUCT((Data!$E$3:$E$5000=$A$3)*(Data!$R$3:$R $5000=E2)*(Data!$N$3:$N$5000))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
This formula is summing ALL the incidences in the N column of what's in
E2. I
want it to sum ONLY those that are in E2 but also also matching what's
in
A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula
works
for other situations.

Thanks,

Steve


  #7  
Old January 10th, 2010, 04:38 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default SumProduct not right

Good eyes. That was it.

Thanks,

Steve

"Lars-Ã…ke Aspelin" wrote:

On Fri, 8 Jan 2010 13:49:01 -0800, Steve
wrote:

This formula is summing ALL the incidences in the N column of what's in E2. I
want it to sum ONLY those that are in E2 but also also matching what's in A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


It seems you have forgotten a comma. Try this:

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3),--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

Hope this helps / Lars-Ã…ke

.

  #8  
Old January 10th, 2010, 04:41 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default SumProduct not right

This works too. Much thanks.
In fact, I can understand the *'s better than the dashes, so I'll probably
use this one. Question: The dashes and the asterisks , are they always
interchangable, with the slightly different positions ?

Thanks again,

Steve

"Ashish Mathur" wrote:

=SUMPRODUCT((Data!$E$3:$E$5000=$A$3)*(Data!$R$3:$R $5000=E2)*(Data!$N$3:$N$5000))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Steve" wrote in message
...
This formula is summing ALL the incidences in the N column of what's in
E2. I
want it to sum ONLY those that are in E2 but also also matching what's in
A3.

=SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000)

I can't figure out why it's not working, because a similiar formula works
for other situations.

Thanks,

Steve


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.