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

#### redspanna

##### Well-known Member
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....
 Bookie Back Odds W/L Return Profit Bet123 5 12.5 W 62.50 57.50 Bet123 10 2.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...

 Bookie Deposit Withdrawal Liability Returns Balance Bet123 5.00 0.00 10.00 62.50 57.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

### 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
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

Replies
6
Views
119
Replies
2
Views
58
Replies
0
Views
102
Replies
0
Views
187
Replies
4
Views
3K

1,126,895
Messages
5,621,493
Members
415,844
Latest member

### 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.

### Which adblocker are you using?

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

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