[Solved] SUMPRODUCT with non-ordered data
[Solved] SUMPRODUCT with non-ordered data
I have a "scores and standings" spreadsheet (attached). I can use SUMPRODUCT to calculate the won-lost-tied and percent-won for a team versus all the other teams it plays. But I can't figure out how to SUMPRODUCT for a team versus only the other teams in its group that it plays.
It looks like SUMPRODUCT only works on ordered data. The list of teams in a group is not ordered against the list of all teams played.
Any help? It's probably easiest to look at my attachment. See the example and questions at the bottom. Thanks.
It looks like SUMPRODUCT only works on ordered data. The list of teams in a group is not ordered against the list of all teams played.
Any help? It's probably easiest to look at my attachment. See the example and questions at the bottom. Thanks.
Last edited by AndyNoCo on Fri Oct 06, 2017 3:44 am, edited 1 time in total.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: SUMPRODUCT with non-ordered data
I think you need to add an auxiliary column marking with a 1 or 0 whether the game is within-platoon or not. I don't have time work up an example at the moment but I'll try tonight.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: SUMPRODUCT with non-ordered data
I hadn't thought of that. It could be a hidden column. But I imagine it also involves LOOKUP, or HLOOKUP, or VLOOKUP, or OFFSET, all of which are way beyond my feeble capabilities.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: SUMPRODUCT with non-ordered data
Take a look at what I did in columns AY and AZ. I used COUNTIF() to mark games between Platteville and teams of the same squad. This sort of thing is prone to errors if, for example, there is a trailing space in one of the cells.
- Attachments
-
- Scores and Standings_FJCC.ods
- (31.3 KiB) Downloaded 88 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: SUMPRODUCT with non-ordered data
Here's a quick pass at modifying your formula to include a test for whether the game is within the platoon, without using any helper ranges (not that I recommend this approach).
I changed the squad header cells to contain only the squad name, so it's easy enough to compare the "against" name with the header range using (for example, Platteville) ISNUMBER(MATCH(O6:O26;B5:T5;0)). This works as another range/criterion in SUMPRODUCT, zeroing out any scores outside the group.
There are a number of team name cells that have trailing spaces. The team names must match exactly (not case sensitive) in order for MATCH to work.
I changed the squad header cells to contain only the squad name, so it's easy enough to compare the "against" name with the header range using (for example, Platteville) ISNUMBER(MATCH(O6:O26;B5:T5;0)). This works as another range/criterion in SUMPRODUCT, zeroing out any scores outside the group.
Edit: PS: |
- Attachments
-
- Scores_acknak.ods
- (22.49 KiB) Downloaded 89 times
AOO4/LO5 • Linux • Fedora 23
Re: SUMPRODUCT with non-ordered data
acknak, what an elegant solution. MATCH is a revelation. No helper ranges in hidden columns. And I'm such a fanatic about trailing spaces and such - I can't believe I missed them.
I changed the second criterion for MATCH to the squad name list in the "Standings" section ($B$47 to $B$50). This appears to obviate the need for special number formatting in the squad header row 5.
And ISNUMBER is much cleaner than subtracting COUNTBLANK.
I'll try to apply your brilliant lesson to the percent-won formula, but I may have to come back to you.
I changed the second criterion for MATCH to the squad name list in the "Standings" section ($B$47 to $B$50). This appears to obviate the need for special number formatting in the squad header row 5.
And ISNUMBER is much cleaner than subtracting COUNTBLANK.
I'll try to apply your brilliant lesson to the percent-won formula, but I may have to come back to you.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: SUMPRODUCT with non-ordered data
acknack, I reverse-engneered your elegant won-lost-tied formula for a squad versus its platoon (not versus the whole brigade). It looks like I came up with a formula for a squad's winning-percentage versus its platoon (not versus the whole brigade).
I learned a lot about MATCH and ISNUMBER, and a lot more about SUMPRODUCT. I'm glad you didn't just give me that formula. Thanks for the breadcrumb trail.
I learned a lot about MATCH and ISNUMBER, and a lot more about SUMPRODUCT. I'm glad you didn't just give me that formula. Thanks for the breadcrumb trail.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: [SOLVED] SUMPRODUCT with non-ordered data
I'm just waiting for something I missed to jump up and bite me in the keister. But it looks good for now...
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
Re: SUMPRODUCT with non-ordered data
If you want to be fanatic, automate it (lowers also the tension on your nerves). Apply a conditional formatting formula to those cells that should not have trailing spaces and mark those that do.AndyNoCo wrote:acknak, what an elegant solution. MATCH is a revelation. No helper ranges in hidden columns. And I'm such a fanatic about trailing spaces and such - I can't believe I missed them.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)