Lookup and find all to sum results in another column

[Spheon]

=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

 

[Mike H]

Hi,

Do you mean this

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

Mike

 

[Gary''s Student]

To see how to get multiple returns from a lookup:

http://office.microsoft.com/en-us/excel/HA012260381033.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))

 

[Spheon]

=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]

Hi,

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

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

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

Mike

 

[Spheon]

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!

 

[Spheon]

thanks, got it now

To see how to get multiple returns from a lookup:

http://office.microsoft.com/en-us/excel/HA012260381033.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))

 

[Spheon]

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

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:G1000))
=SUMPRODUCT((Invoices!A5:A1000=A3)*(Invoices!G5:G1000))
=SUMPRODUCT((Invoices!A5:A1000=A4)*(Invoices!G5:G1000))
etc when I drag all the change to this
=SUMPRODUCT((Invoices!A6:A1001=A23)*(Invoices!G6:G1001))



thanks again

 

[Bernd P]

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