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

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,477
Office Version
  1. 2016
Platform
  1. Windows
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....
BookieBackOddsW/LReturnProfit
Bet123512.5W62.5057.50
Bet123102.00-10.00-10.00
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...

BookieDepositWithdrawalLiabilityReturnsBalance
Bet1235.000.0010.0062.5057.50


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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,946
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,895
Messages
5,621,493
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top