=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

Hi,

Do you mean this

=SUMPRODUCT((A2:A5=E2)*(C2:C5))

Mike

"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

To see how to get multiple returns from a lookup:

http://office.microsoft.com/en-us/ex...260381033.aspx

However, in your case, all you want is the sum. Consider using the
SUMPRODUCT() function instead. Perhaps something like:

=SUMPRODUCT((C2:C5=E2)*(C2:C5))
--
Gary''s Student - gsnu200839


"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

You would be better off using SUMPRODUCT, its an array formula but you
don't have to commit it with Ctrl+Shift+Enter, for a detailed and well
explained tutorial check out Bob Phillips site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Spheon;269698 Wrote:
=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the
result to
end at the first find, I want to find all that are the same as E2 in
range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and
getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75209

=SUMPRODUCT(Invoices!A:A=A22,Invoices!G:G)
doesn't work

I am in worksheet B that looks up a project number in column A of worksheet
A against the same match in column A of invoice schedule worksheet A then
when it the contents match in whole column of worksheetb column A it adds ALL
the contects of WSB column G, but not first find , all of the matches. I have
several invoices issued under a project number.

WSB
A B G
proj nr - invoice nr - ,,,,, £3000 (sum invoiced)


WSA
A H
proj nr,,,,,,, total invoiced <<<this formula reqd that looks up
same proj number as wsa in column wsbA and gives me total of where matches of
wsbG

not sure if that explains it I am not too technical, sorry, thanks for help!

sph



"Mike H" wrote:

Hi,

Do you mean this

=SUMPRODUCT((A2:A5=E2)*(C2:C5))

Mike

"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

Hi,

Well that bears no resembelance to the formula I gave you, try this

=SUMPRODUCT((Invoices!A1:A1000=A22)*(Invoices!G1:G 1000))

Note that unless your using Excel 2007 you can't use full columns in
Sumproduct

Mike

"Spheon" wrote:


=SUMPRODUCT(Invoices!A:A=A22,Invoices!G:G)
doesn't work

I am in worksheet B that looks up a project number in column A of worksheet
A against the same match in column A of invoice schedule worksheet A then
when it the contents match in whole column of worksheetb column A it adds ALL
the contects of WSB column G, but not first find , all of the matches. I have
several invoices issued under a project number.

WSB
A B G
proj nr - invoice nr - ,,,,, £3000 (sum invoiced)


WSA
A H
proj nr,,,,,,, total invoiced <<<this formula reqd that looks up
same proj number as wsa in column wsbA and gives me total of where matches of
wsbG

not sure if that explains it I am not too technical, sorry, thanks for help!

sph



"Mike H" wrote:

Hi,

Do you mean this

=SUMPRODUCT((A2:A5=E2)*(C2:C5))

Mike

"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

BRILLIANT :) I have got 2007, did have to do range though A5: etc as first
few rows are headers.....but worked!

thank you so much, easy when you know how eh!

appreciated, you have saved my hair becoming a bald patch!

:)

"Mike H" wrote:

Hi,

Well that bears no resembelance to the formula I gave you, try this

=SUMPRODUCT((Invoices!A1:A1000=A22)*(Invoices!G1:G 1000))

Note that unless your using Excel 2007 you can't use full columns in
Sumproduct

Mike

"Spheon" wrote:


=SUMPRODUCT(Invoices!A:A=A22,Invoices!G:G)
doesn't work

I am in worksheet B that looks up a project number in column A of worksheet
A against the same match in column A of invoice schedule worksheet A then
when it the contents match in whole column of worksheetb column A it adds ALL
the contects of WSB column G, but not first find , all of the matches. I have
several invoices issued under a project number.

WSB
A B G
proj nr - invoice nr - ,,,,, £3000 (sum invoiced)


WSA
A H
proj nr,,,,,,, total invoiced <<<this formula reqd that looks up
same proj number as wsa in column wsbA and gives me total of where matches of
wsbG

not sure if that explains it I am not too technical, sorry, thanks for help!

sph



"Mike H" wrote:

Hi,

Do you mean this

=SUMPRODUCT((A2:A5=E2)*(C2:C5))

Mike

"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

thanks, got it now :)

"Gary''s Student" wrote:

To see how to get multiple returns from a lookup:

http://office.microsoft.com/en-us/ex...260381033.aspx

However, in your case, all you want is the sum. Consider using the
SUMPRODUCT() function instead. Perhaps something like:

=SUMPRODUCT((C2:C5=E2)*(C2:C5))
--
Gary''s Student - gsnu200839


"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

=SUMPRODUCT((Invoices!A5:A1000=A22)*(Invoices!G5:G 1000))

one final thing, how can i enter this formula in the spreadsheet so I can
drag it where the A22 only changes relative to the cell ie

=SUMPRODUCT((Invoices!A5:A1000=A2)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1 000))
etc when I drag all the change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G 1001))

:(

thanks again

"Spheon" wrote:

=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the result to
end at the first find, I want to find all that are the same as E2 in range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance

Like this:
=SUMPRODUCT((Invoices!$A$5:$A$1000=A22)*(Invoices! $G$5:$G$1000))

But i don't think it will serve as you expect!
Spheon;269733 Wrote:
=SUMPRODUCT((Invoices!A5:A1000=A22)*(Invoices!G5:G 1000))

one final thing, how can i enter this formula in the spreadsheet so I
can
drag it where the A22 only changes relative to the cell ie

=SUMPRODUCT((Invoices!A5:A1000=A2)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1 000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1 000))
etc when I drag all the change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G 1001))

:(

thanks again

"Spheon" wrote:


=IF(ISNA(VLOOKUP(E2,$A$2:$C$5,3,FALSE)),0,VLOOKUP( E2,$A$2:$C$5,3,FALSE))

Hi I have managed to get this formula to work but I dont want the
result to
end at the first find, I want to find all that are the same as E2 in
range to
sum all in column 3.

Is this possible, I dont really want to create a pivot table and
getdata
etc, surely there is a way but I am scratching my head :(

thanks in advance




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75209

Hello,

You can use my UDF Sfreq and get rid of all maintenance because new
project numbers will be shown automatically if you choose your output
area large enough:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd