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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|