Lookup Formula?

[lejohnson93]

I am trying to do some analysis on sports gambling. I'm using multiple discords to bet on multiple sports.

In my excel document, I have row D where I input the sport I am placing a bet on (Tennis, Golf or Soccer). Row G is the discord that I placed the bet from (Juiced Bets Discord, BetRecaps, or Twitter). Row H is the amount that I have wagered, and Row I is the amount that I have won.

I'm trying to see if there's a way to pull the amount I've wagered from a specific source on a specific sport. As an example, I'd like to see, when I am placing a bet on soccer from the juiced bets discord, how much have I won/lost. Same thing for Tennis, same thing for golf. Then I'd like to do that for BetRecaps, and Twitter as well. So I'd have 9 results to analyze.

This feels difficult to explain, but hopefully, that makes sense!

 

[Burrgogi]

I am trying to do some analysis on sports gambling. I'm using multiple discords to bet on multiple sports.

In my excel document, I have row D where I input the sport I am placing a bet on (Tennis, Golf or Soccer). Row G is the discord that I placed the bet from (Juiced Bets Discord, BetRecaps, or Twitter). Row H is the amount that I have wagered, and Row I is the amount that I have won.

I'm trying to see if there's a way to pull the amount I've wagered from a specific source on a specific sport. As an example, I'd like to see, when I am placing a bet on soccer from the juiced bets discord, how much have I won/lost. Same thing for Tennis, same thing for golf. Then I'd like to do that for BetRecaps, and Twitter as well. So I'd have 9 results to analyze.

This feels difficult to explain, but hopefully, that makes sense!

Hi, welcome to the forum.

Your use of the term "row D" is quite confusing. In Excel, rows are numbered 1 thru 1,048,576. The following screenshot displays row 3 that is highlighted.

On the other hand, columns have letter references. A, B, C, D, etc. In the screenshot below column D is highlighted.

So with that out of the way, please clarify for us where are your sports names are contained in? Are they located in rows or columns? If it's too hard to explain, take a screenshot of your worksheet and post it here.

 

[lejohnson93]

Very sorry about that! Column D, Column G, Column H are the main 3 columns.

 

[Burrgogi]

It is unclear what you are trying to achieve. You said earlier: "Row G is the discord that I have placed the bet from (Juiced Bets Discord, Bet Recaps...etc)." This is a mystery to me. What is "discord" in this context? Are you saying that col. G contains the wager amounts you have placed on each of those websites?

Like I said before, upload a screenshot of your worksheet or you can use XL2BB (a free & smart add-in) to display what your sheet looks like.

 

[lejohnson93]

Here's a visual that will hopefully help with what I'm trying to accomplish.

The Excel sheet below is how I am manually keeping track of bets that I am making. I keep track of the dates that the betting event is taking place (C), if it's Tennis, Golf or Soccer (D), The actual bet that I made (E), the Sportsbook that I am placing the bet on (F), the place that I got the bet from (G), The amount that I wagered (H), and the amount that I won (I).

For more clarification on G - I am paying to use sites that will recommend bets for you. They post plays, and I am simply plugging them into the various sportsbooks.

What I would like to do, is figure out a way to see how the specific bet sources are performing for the three potential tags (Tennis, Golf or Soccer). You can see the empty sections of the chart over on the right that I would like to fill in. P2476-P2481, Q2476-P2481, and R2476-R2481.

I basically need a formula that will say if the Tag is Soccer and the bet source is BR Gamelines, across the entire document, how much have I wagered, and how much have I won. Once I have that, I should be able to modify the formula to fit all of the various Bet Sources and Tag's that I am trying to track.

Hopefully this helps, and thank you for replying!

 

[Burrgogi]

OK now the picture is coming in more clearly on what you're trying to do. For your needs, I would be inclined to use a pivot table. They're not that difficult to setup and once it's created, you can easily see your earnigns & losses based on either sports category or by betting website. You shouldn't have to start from scratch. You can keep your current column headers and your current data and just convert it to a pivot table. However, I'm a little concerned about your row of data beginning at row 2463 -- not sure what kind of data you have above that row (not reflected in the screenshot). It might create some conflict with converting things over to a pivot table.

They say that a picture is worth a thousand words. You can google up "ms excel how to create pivot table" and you'll get a lot of hits. But a better option would be to search on Youtube for pivot tables so you can see it in work in action. I think you'll like it once you see how it works. It would also eliminate the need to have a separate small table like you've shown in your screenshot.

If you want to forgo the pivot table option and keep things the way they are, I think using the AGGREGATE function in a formula would be the best option to keep track of those sports (or 'tags' as you say). There are some members here who are highly skilled with formulas. Let's see if they agree with me on this - they might have better ideas.

 

[lejohnson93]

OK now the picture is coming in more clearly on what you're trying to do. For your needs, I would be inclined to use a pivot table. They're not that difficult to setup and once it's created, you can easily see your earnigns & losses based on either sports category or by betting website. You shouldn't have to start from scratch. You can keep your current column headers and your current data and just convert it to a pivot table. However, I'm a little concerned about your row of data beginning at row 2463 -- not sure what kind of data you have above that row (not reflected in the screenshot). It might create some conflict with converting things over to a pivot table.

They say that a picture is worth a thousand words. You can google up "ms excel how to create pivot table" and you'll get a lot of hits. But a better option would be to search on Youtube for pivot tables so you can see it in work in action. I think you'll like it once you see how it works. It would also eliminate the need to have a separate small table like you've shown in your screenshot.

If you want to forgo the pivot table option and keep things the way they are, I think using the AGGREGATE function in a formula would be the best option to keep track of those sports (or 'tags' as you say). There are some members here who are highly skilled with formulas. Let's see if they agree with me on this - they might have better ideas.

Awesome. Thank you! The data up above is very similar to what you see in the screenshot. I just tried to find a section that I thought would be help to be a visual. I will look into your two options you provided and mark this as answered if I am good, or I'll reply if I'm still having trouble. Thank you!!

 

[Burrgogi]

Here's yet another option using SUMIFS formula:

Plug this into cell P2476:

=SUMIFS(H:H, D:D, "Tennis", G:G, "BR Gamelines")

Plug this into cell Q2476

=SUMIFS(I:I, D:D, "Tennis", G:G, "BR Gamelines")

 

[Burrgogi]

Here's yet another option to consider:

Click on the very 1st row of your worksheet (where your column headers are). With row 1 highlighted, click on the 'Data' tab in the ribbon. Then click on Filter as shown in the screenshot.



You will see little arrows next to each of those column headers. Now you can start filtering.

Click on the arrow for column D - choose your sport. Say tennis as an example. You can set criterias for other columns as well. So if you want to show just "BR Datagolf" for column G, you can do the same thing by clicking on the little arrow. Excel will display your list based on those chosen criterias.

You might have noticed the Total shown in my screenshot. That total figure will change based on the filtered list so if you choose just "Tennis" in column D, it will change to $0 in the Result column.

Example:

Here is the formula for that:

For the 'Wager' column:

=SUBTOTAL(9, H:H)

For the 'Result' column:

=SUBTOTAL(9, I:I)

The benefit of using the SUBTOTAL function over the SUMIF or SUMIFS is that the SUBTOTAL recalculates the total figure based on your filtered list so it's dynamic.
You can copy & paste the formula I gave you where ever you wish in the spreadsheet that suits your preferences.

 

[Burrgogi]

One final comment: The filter feature & SUBTOTAL function is nice but I think you'll like the pivot table better. Achieves the same result but in a more spiffy way.