Sum of profit for a user using lookup

[artkiller99]

Hey,

Say I have 2 sheets, one with accounts containing user account number and name and I wish to find the total sum of profit/loss made by all users. the second sheet contains the account number I use for the lookup and the profit which can be negative and positive. how would I find the actual sum of all trades instead of just one?

Using =SUM(LOOKUP(A596,Sheet2!B:B,Sheet2!C:C)) gives me just one and it seems to be incorrect anyway, help?

Thanks

 

[AOB]

Use SUMIF (or SUMIFS if there are multiple criteria required)

See here for SUMIF and here for SUMIFS

 

[AOB]

You will probably find it easier, if you are working across multiple sheets, to name your ranges first, then refer to the named ranges in your SUMIF(S) function rather than quoting specific sheet names and cell references each time. If your workbook is 'live' (i.e. the lookup data may grow or shrink over time), then you should make those named ranges dynamic (so they also grow or shrink as you add or remove more accounts / names etc.)

 

[artkiller99]

Thanks I'll give it a try now, and yeah the data is live in a matter of speaking since I may have to pull data for only a day of trades (maybe 50 cells) or for months (tens of thousands). I'll try see if I can name them.

Thanks

 

[AOB]

Cool, mess about with it and see how you get on. If you need a hand naming the ranges dynamically, let me know.

 

[artkiller99]

Hey, thanks for the sumif tip, did the job :D One other thing Im struggling with is a similar situation but with time. Next to each trade I have in "d:hh:mm:ss" format the duration was open for, with looking up each client in particular can I find the average time they kept each trade open? I keep getting a #DIV/0! error in almost every case I try. Any suggestions?

Much appreciated

 

[AOB]

Can you post some sample data and the formula you're using that returns that error?