Help with formula to include all data rows - =SUMPRODUCT maybe?

[redspanna]

Hi all

I'd like some help with formula to calculate sums for all data rows. I have them for just 1 row but unsure how to change them to incorporate data through whole sheet as required


so data is held on sheet called Bets and very simply it looks like this....
[table="width: 500, class: grid"]
[tr]
[td]Bookie[/td]
[td]Back[/td]
[td]Odds[/td]
[td]W/L[/td]
[td]Return[/td]
[td]Profit[/td]
[/tr]
[tr]
[td]Bet123[/td]
[td]5[/td]
[td]12.5[/td]
[td]W[/td]
[td]62.50[/td]
[td]57.50[/td]
[/tr]
[tr]
[td]Bet123[/td]
[td]10[/td]
[td]2.00[/td]
[td][/td]
[td]-10.00[/td]
[td]-10.00[/td]
[/tr]
[/table]
so as you can see from the above two examples, the 1st line ..
Bet123 had a 5.00 bet placed at odds of 12.5, it won so return was 67.50 with profit of 57.50 (67.50 less 5 initial bet)

line 2...

Bet123 has an unsettled bet of 10.00 with odds of 2.00, as its unsettled the W/L column is blank and the return is set at -10.00 which is the liability of this open bet, ie how much I stand to lose


now on sheet 2 I have a separate table that shows data collected from the Bets sheet, and simply looks something like this...

[table="width: 500, class: grid"]
[tr]
[td]Bookie[/td]
[td]Deposit[/td]
[td]Withdrawal[/td]
[td]Liability[/td]
[td]Returns[/td]
[td]Balance[/td]
[/tr]
[tr]
[td]Bet123[/td]
[td]5.00[/td]
[td]0.00[/td]
[td]10.00[/td]
[td]62.50[/td]
[td]57.50[/td]
[/tr]
[/table]


the above table is populated using the following details..


Bookie - this is a simple Pivot table that shows a list of all individual bookies on my Bets sheet
Deposit - formula used to get info from another sheet =SUMPRODUCT(--(Natwest!I6:I5812=B2),Natwest!J6:J5812)
Withdrawal - formula used to get info from another sheet =SUMPRODUCT(--(Natwest!I6:I5812=B2),Natwest!K6:K5812)


here is where I'd like the help:

liability - search through column A of the Bets sheet for matching bookie name to that in Sheet1 B2,
if any of the cells in column D (the W/L column) of the Bets sheet are blank then display the sum of all the cells though column B (the back column). if all cells in column D have a value then display the value of zero in this Liability column

Returns - search through column A of the Bets sheet for matching bookie name to that in Sheet1 B2,
Sum all the values through column E (Returns) in the Bets sheet only for those where column D (W/L) are NOT blank

Balance - search through column A of the Bets sheet for matching bookie name to that in Sheet1 B2,
Sum all the values through column F (Profit) in the Bets sheet only for those where column D (W/L) are NOT blank


so with all given formula the complete table will be the same as the one above with the required totals for each of the columns shown, Liability, Returns and Balance


Hope I've explained this ok, any questions please ask, and really appreciate help in advance

 

[mikerickson]

First, the numbers in the first spreadsheet don't match the words. (Return of 62.50??)

SUMFIS should do what you wwant


=SUMIFS(BETS!$E:$E, BETS!$A:$A,"Bet123", BETS!$E:$E,"<>") would give you the Return.
=SUMIFS(BETS!$E:$E, BETS!$A:$A,"Bet123", BETS!$E:$E,"") for the Liability