Solved: Sum If I think

[coliervile]

I have a worksheet to keep a scorecard at baseball games. What I need is when a player is substituted for another is how to keep track of their hits, stikes, balls and so on in columns "CQ"-"DB". Each player has a player number listed in column "A" and in the bottom right hand corner of each inning column. I think I need some sort SUM IF statement to do this, but it's well above my head and can this go into a macro? Thanks for your help.

Best regard,

Charlie

[Bob Phillips]

This counts how many 10's there are

=SUMPRODUCT(--(MOD(COLUMN($E$11:$CO$11),10)=2),--($E$11:$CO$11=$A4))

[coliervile]

Thanks for your reply "XLD". I didn't explain what I need very well. In the nine innings that are on the score card player number 10 played in innnings 1 through 3, player 15 played in innnigs 4 through 7 and player 5 played in innings 8 through 9. In the third innning player 10 got 1 hit (a single) as indicated in cell "Y5". Player 15 got two hits (2 singles), in innings four and six as indicated in cells "A15" and "BC5". Player 5 got three hits (3 singles), 2 in inning 8 and 1 in inning 9 as indicated in cells "BW5", "BX5" and "CG5". So all of the players got a total of six singles. Here's what I need...in column "CT"- cell "CT4 the total number of hits-1(singles) for player 10, cell "CT6 the total number of hits-2 (singles) for player 15, and cell "CT8 the total number of hits-3 (singles) for player 5.
How can I get the totalnumber of hits (singles) for each individual player??? Take a look at the newworksheet. I hope this explains it better.

Best regards,

Charlie

[nsaint]

I found this SUMIF similar function with offset capability and have used it several times. I have included it within the sheet and you should be able to see how to use it for all the other cells as well.

I also included a 2 variable match SUMIF type function with offsets that will match on two conditions.

I hope you find this useful

[coliervile]

Thanks "nsaint" for your suggestion. I looked it over and in cell CT8 player number 5 should have three (3) hit total. Player 5 had 2 hits in inning 8 and 1 hit in inning 9. Is there a way around this???

I came up with this, but it's a lot of work....take a look.

Best regards,

Charlie

[nsaint]

The offset values are based on the cell, so you can use the same formula and add it again, but offset by one row or col in this case...ie =MatchSumOffset(CP8,A10:CO11,-7,-6) + MatchSumOffset(CP8,A10:CO11,-6,-6)

Here is the syntax MatchSumOffset(value_to_match, range_to_look_for_match, col_offset, row_offset), The col and row offset are the values used to point to a location that has values that will be summed.

[coliervile]

Okay, I think I've got and understand it. Thanks for you help with this.

Best regards,

Charlie

[coliervile]

I think I have it. One question the -7, -6 what are they counted off of or from to get to the cell/s you need?

Best regards,

Charlie

[nsaint]

They are in ref to the value to match. In Cell CP8 I am looking for the value in cell CT8 or 5 in this case. I then offset back 7 col and up 6 rows to get the 1's you have in there. You would do the same thing for the other cells as well. Just remember the offset values are in reference to the cell containing the item youare trying to match - in this case cell CP8. Anything to the right and down would be a positive offset number and anything to the left and up is a negative offset number.

I added the attachment. In the 9th, player 5 had a double and you should see how this might work.

[coliervile]

Okay, I think I understand this. One problem I have run into is that the formula doesnt seem to update the totals when the ininng totals are changed, lets say player 5 actally had 3 singles in the 8th ininng and not 3. When I changed one of the 1's to a 2 the total didn't change to a 3???

Best regards,

Charlie

[nsaint]

Yes, you will have to force the function to do a recalculation. There are several ways to do this, but the quickest way to effect this for all rows is to toggle the value in the "value_to_match" criteria - in this case player 5. I know there is a better way to accomplish this - progrmatically, I just haven't had the time to put it in the code.

[coliervile]

I don't understand the toggle part of you statement? Can you briefly explain a bit more? Thanks again for you time with this.

Best regards,

Charlie

[coliervile]

I got the recalculation part CTRL+ALT+F9 to update the cell/s.

Best regards,

Charlie

[nsaint]

O.K. I added the code to update the calculations if something changes.

Take a look.


I hope this helps, but it also forced me to add this auto calculation as well

[coliervile]

"nsaint" it works really great and I'll let you know how it all comes out. Thanks again for all your time and hardwork. I'll amrk this as solved.

Best regards,

Charlie

[Bob Phillips]

Put 5 in Ct1 and then use this array formula

=SUM(IF(N(OFFSET($L$1,10,{0,10,20,30,40,50,60,70,80},1,1))=$A4,
N(OFFSET($E$1,CT$1-1,{0,10,20,30,40,50,60,70,80},1,1))+
N(OFFSET($F$1,CT$1-1,{0,10,20,30,40,50,60,70,80},1,1))))

[coliervile]

XLD thanks for youe reply. I'm trying to figure out your formula and exactly where I would need to put this for each player and for 1B, 2B, 3B, HR, BB, and HBP? Also would there be adjustments neccessary to the formula for each cell listed above?

Best regards,

Charlie

[Bob Phillips]

Charlie,

Put it in CT4. It is written so that you can copy and pste it into CT6 and CT8, no need to re-enter.

Because the results types (1B etc.) are horizontal and the data is vertical, I added a simple aid to align them. That is why I said enter a 5 in CT1. Similarly for 2B enter 6 in CU1, 3B enter 7 in CV1, HR enter 8 in CW1, BB enter 3 in CX1,and for HP enter 4 in CY1. You can then copy CT4:CT8 across the other columns..

[coliervile]

Okay I'll give it a try.

Best regards,

Charlie

[Bob Phillips]

So do you want to tell the folks at MrExcel as well that you posted the same request in both places and there have been sets of people at both trying to solve the problem, and so you have been wasting all their time?

[coliervile]

Yes I have a posting on mrexcel when I was attempting to figure out how to add up runs vs. RBI's for each player. On my worksheet above in this post I have an area that's blue in color that was meant for the each player runs per inning. I had changed what the blue area represented to a players RBI's. The difference between the two, runs and RBI's is RBI's are the run or players the person batting brings in with his bat vs.runs- when a player himself crosses the plate. When I changed the meaning after posting my mrexcel I realized that I could use "nsaint's" idea, which I have. Thank you "nsaint" and "xld" for your ideas. I'm a rookie at this but I enjoy learning more things about macros and what they can do.

I'm well aware of the double posting on this site and mrexcel. It was not my intentions to have a double posting. Given the fact that I used my same name screen "coliervile" on both and the same worksheet would not be the brightest idea if one were going to double post.

I use this forum when I'm needing help with macros and mrexcel when I need help more so with formulas. That's not to say that I haven't used mrexcel with some macro help as well. I greatly appreciate all those who have helped me, both here and mrexcel, learn about excel and all of its magic. I've read on here before how irritated the mentor's get when someone has double posted something. I apologize if you found my mrexcel posting offensive, but it wasn't intentional. "xld" you've helped me in the past and I hope that you continue to do so.

Best regards,

Charlie

[coliervile]

Here's what I have for the worksheet...

Best regards,

Charlie

[Bob Phillips]

Well I gave you a formula solution which works because I tried it.

[coliervile]

Thanks andas I said before sorry for my error.

Best regards,

Charlie

[nsaint]

Charlie, if you paste the code below into "ThisWorkbook" in the VBIDE (Alt +F11) it will send the Ctrl+Alt+F9 update/recalculation any time somethigs changes on the worksheet. So the values get updated as you type them.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.SendKeys ("^%{F9}")
End Sub

[coliervile]

Thanks "nsaint".

Best regards,

Charlie