Lookup and find all to sum results in another column

  • Thread starter Thread starter Spheon
  • Start date Start date
S

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

:)
 
=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
 
Back
Top