I'm setting up a golf tournament and need help assigning dollar winnings to cells and then when I put someone's name in those cells it will auto calculate a running tally for me for each player for the week.

Example:

1. Let's say Steve wins 3 of the 6 skill events on Monday. Each are worth $10. I want to be able to put Steve's name in Excel for the events he won and then have it auto calculate a running total for me in another cell.

2

3 Answers

With some slight adjusting of the layout, you can use SumIf()+SumIf()...

=SUMIF($C$17:$C$33,J16,$B$17:$B$33)+SUMIF($D$17:$D$33,J16,$B$17:$B$33)+SUMIF($E$17:$E$33,J16,$B$17:$B$33)+SUMIF($F$17:$F$33,J16,$B$17:$B$33)+SUMIF($G$17:$G$33,J16,$B$17:$B$33)+SUMIF($H$17:$H$33,J16,$B$17:$B$33)

Basically, you SumIf([sunday range],[Name],[dollar prize rng]) and just add another for Monday, then Tuesday, etc.

0

A much simpler formula would be to use SUMPRODUCT.

Using BruceWaynes new layout above, the formula in each cell in column K would be as follows (where n is the row number where you are entering the formula)

=SUMPRODUCT((B:H=Jn)*(B:B))

For example

Steve would be =SUMPRODUCT((B:H=J16)*(B:B))

Chris would be =SUMPRODUCT((B:H=J17)*(B:B))

And so on...

4

Let's see if I understood your question correctly. Let's say Steve wins 3 events each worth $10. Now I'm not exactly sure which cells represent these event's, so I'll just make my own examples.

You can use the COUNTIF(range, criteria) function for this as well as the SUMIFS()

Say you mark the fact that Steve wins the daily lf on Monday by entering Steve in C17 or similar.

Then, the "Total" cell behind Steve H16(or wherever you want it), you could enter Something like:

 =COUNTIF(B16:F31;G16)*10

And change the formatting of the cell (by right clicking or in the main ribbon) to show currency.

This will then count the amount of "Steve" and multiply that number with 10.

If you want different events to have different values, and suppose your values are in the B column, and all columns are shifted to the right, you could easily change this to something like:

 =COUNTIF($C$16:$G$20;H16)*$B$16+COUNTIF($C$21:$G$21;H16)*$B$21+COUNTIF($C$22:$G$24;H16)*$B$22+COUNTIF($C$25:$G$27;H16)*$B$25+COUNTIF($C$28:$G$30;H16)*$B$28+COUNTIF($C$31:$G$31;H16)*$B$31

And then drag it down to cover each name. The downside is a rather long function that is not the easiest to work with. You could also have it without the B column, and have the values directly in the function, like the first example.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy