Correlating data: Which function(s).

If I understand correctly, SUMIF should work. If your first list is A1:B300 and your list of names is D1:D20, E1 can have =SUMIF(A$1:A$300,D1,B$1:B$300)
If this is not right, could you please post a workbook showing what you mean?

Hello darkyam,

You made better sense out of my message than you anticipated. I tried and it works perfectly. I just need to create a little routine to test for all scores/options... thank you

Now I've been advised to ask my question one at a time and this was always going to be a multi-parter, so can I throw in an extra facet and ask for advice please:-

I actually have TWO main lists, following the example of A1:B300 there is another two colums C1:D300 which are the same format as the first list (names in the first column, scores in the second). This is because, naturally, each team needs to play another and that teams scores also need to be manually entered.

As this second list contains the same 20 team names as the first, I need to combine the results in E1. Is there an addition/modification I can make to this formula or would I need to combine the results in some kind of holding see (if that makes sense)?.

Thanks
J.
PS: darkyam, I did previously upload a sample w/sheet, but it was suggested (quiet rightly) that it was rather large and a bit of an imposition to expect members to download it. So I'm trying my best with descriptive text.

Simply put two SUMIFs in the formula. =SUMIF(A$1:A$300,F1,B$1:B$300)+SUMIF(C$1:C$300,F1,D$1:D$300)

Guess what this means

Thank you darkyam.

I won't marked this solved yet, as there are a couple of more aspects to add , but I need to work out how to describe them.

Can I ask one thing in the meantime... is there a way to prevent the zero displaying in the result list, if scores haven't yet been entered agains a team?

Thanks.

There are two ways:
1. Another IF function: If(SUMIF1+SUMIF2=0,"",SUMIF1+SUMIF2)
2. Custom format of #;;

I'll have a go at working out option 2. If I eventually add more functions to this expression it could finish up extremely long.

Very many thanks for your help darkyam... much appreciated.
J.

Originally Posted by DonkeyOte

FWIW, given the layout of your data and the fact that the data types are alternating I would say you could actually get away with a single SUMIF:

=SUMIF($A$1:$C$300,$F1,$B$1:$D$300)

Brilliant, that is working too. Thank you DonkeyOte.

This is what made me hesitant about breaking-up my questions, because I didn't want people to have to double work my questions... so I apologise for that, but it is helping me learn (slowly ).

Thanks guy's,
J.

Hi all,

The advice above is all working for me. Now I need help (please) to expand this formula:-

=SUMIF($C$18:$E$397;$H18;$D$18:$F$397)

Where Col C and Col E = team names. Col D and Col F = team scores

I want to achieve a sum count for each team when they play a match i.e.: total of matches played for each team.

I assume (please correct me if I'm off track!) that the way to go is to find 'any' entry that is 'not blank' (a team might score 0!) in the scores column.

Any suggestions please?

Thanks,
J.

Is the schedule entered as teams play games (i.e., nothing is entered at all until a match has actually been played) or is it entered ahead of time with just the scores filled in?

If the former, just change SUMIF to COUNTIF and delete the last argument. If the latter, try =SUMPRODUCT(($C$18:$E$397=H18)*($D$18:$F$397<>""))

Hi again darkyam,

The team names are filled in the beginning of each season from a VLOOKUP, the number of team names being dependant on another fixed formula table. The scores entered on a weekly (as played basis).

The SUMPRODUCT worked perfectly . Thanks yet again.

I'm elated and a tad dissapointed, because I thought I would possibly be adding another function to the original formula, which could have led me on the path of helping myself to work out expressions for other columns, but elation rules

So can I ask:- I have, as I say other calulations to perform from the entered results. The next are going to be:-

1. count the 'wins' for each team which in effect is any score greater than 4.
2. count the 'loses' ....... any score less than 4.

Will these mean adding another function to the first formula. If so are they things that I can try to work out myself... with a hint.

Thanks,
J.

What do you want to happen if the score is exactly 4?

To answer the question, though, if a win is 4 points or more, you'd use =SUMPRODUCT(($C$18:$E$397=H18)*($D$18:$F$397>=4)) for the wins. If it's only more than 4 points, then replace >= with >. Losses would simply be games - wins.

Very sorry darkyam, my mistake for getting too excited

There a 9 games to a match set so the trigger between win and loose is 5 not 4.

I'm off to try your suggestion (with 5 )... thanks again.
J.

Kick the cat again. I'm sure it's withholding the answer from you.

Seriously, though, why are you copying the formula down past your list of names? If you want it to return blank, a simple =IF(H18="","",<current formula>) should work, but I don't see the point in having it go beyond your list of names since you know the teams that will be playing from the beginning of the season.

Hi darkyam,

I'll try to explain...

The list of team names is populated according to how many team are playing in any given season. The fixture formula allows for between 4 - 20 teams. So if there are only ten teams playing the list gets populated accordingly with:-

=IF(ROW(H18)-17<=Setup.$E$11;Setup.C18;"")

It's really for tidyness and later printing... I hope.

Does that make sense?

Here I go to try the suggestion... I'll report back.

Cats left home and the real bad news is I'm giving up pool and taking up darts next weeks

Thanks
J.

Could you post an updated workbook? The formulas should work. And don't worry, I don't have a data plan to worry about. Just please don't zip it, as I seem to have problems opening any files from here that are zipped.

Good afternoon all,

Hi darkyam,

I 'think' I've cracked it and the cat has come back home

I'm not 100% sure I've adapted the formulas optimally, but it seems to be working as expected (testing of different scenarios... on-going).

I'm happy to upload if you still wish...?. Along with the testing, I'm now thinking how to create a couple of print-out and a website format (the cat won't be around much longer )

Thanks again,
J.

If it's working, no need to upload it. I would, however, suggest that additional questions be asked in a new thread. The reason is, when contributors here see a thread with over five responses, they tend to assume that there's a lot of history for them to understand before they can help and that a solid dialogue regarding the situation is already taking place, so many of them ignore those threads and focus on ones with few responses. Starting new threads will get you help more quickly if I don't happen to be online at the time. If the question is particularly relevant to this thread, you can always post a link back here.