I'm organizing a buddy's sports betting spreadsheet and am trying to get my SUMIFS formula to work.

On the Data Ref sheet, I'm trying to sum a cell based on three different criteria. Player, Bet Type, and Live/Pre Match.

Here is the formula I have come up with.

Player - Bautista-Agut, Bet Type - Parlay, Live/Pre - Pre Match

When I use this formula it works perfectly.

=sumifs(Main!H$3:H,Main!D$3:D,"*Bautista-Agut*",Main!$E$3:$E,$D35,Main!$C$3:$C,$E35)

However, I don't want to have to go back through each of the 260 cells and change the name of each player with "**". I want to simply reference the cell in column c.

When I try to reference a cell with the player's name in it the formula doesn't work. I think this is because on the main spreadsheet some cells in the column I'm referencing has more than one player in it and the cell is only looking for the one player name.

Is there another way to perform these SUMIFS by referencing a cell? I've tried REGEXMATCH(), INDEX(), MATCH() nested within the SUMIFS but still couldn't get them to work.

You can view my spreadsheet here.

Thanks for taking the time to read my question. Also, if there are any tips on how I've structured my data I'd love to hear them. I'm brand new to these types of formulas.

CodePudding user response：

I don't want to have to go back through each of the 260 cells and change the name of each player with "**". I want to simply reference the cell in column c.

You can replace the name with "*"&C2&"*":

Instead of:

=sumifs(Main!H$3:H,Main!D$3:D,"*Albot*",Main!$E$3:$E,$D2,Main!$C$3:$C,$E2) 

Use:

=sumifs(Main!H$3:H,Main!D$3:D,"*"&C2&"*",Main!$E$3:$E,$D2,Main!$C$3:$C,$E2)