[Solved] SUMPRODUCT with non-ordered data

Discuss the spreadsheet application
Post Reply
User avatar
AndyNoCo
Posts: 33
Joined: Wed Sep 27, 2017 9:43 pm
Location: Colorado, USA

[Solved] SUMPRODUCT with non-ordered data

Post by AndyNoCo »

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.
Scores and Standings.ods
(44.36 KiB) Downloaded 96 times
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Post by FJCC »

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.
User avatar
AndyNoCo
Posts: 33
Joined: Wed Sep 27, 2017 9:43 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Post by AndyNoCo »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Post by FJCC »

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.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: SUMPRODUCT with non-ordered data

Post by acknak »

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.
 Edit: PS: 
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.
Attachments
Scores_acknak.ods
(22.49 KiB) Downloaded 89 times
AOO4/LO5 • Linux • Fedora 23
User avatar
AndyNoCo
Posts: 33
Joined: Wed Sep 27, 2017 9:43 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Post by AndyNoCo »

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.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
User avatar
AndyNoCo
Posts: 33
Joined: Wed Sep 27, 2017 9:43 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Post by AndyNoCo »

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.
MacBook Pro (Retina, 15-inch, Mid 2015), macOS Mojave 10.14.3, Apache OpenOffice 4.1.6, Legacy Java 6
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [SOLVED] SUMPRODUCT with non-ordered data

Post by acknak »

Good work!
AOO4/LO5 • Linux • Fedora 23
User avatar
AndyNoCo
Posts: 33
Joined: Wed Sep 27, 2017 9:43 pm
Location: Colorado, USA

Re: [SOLVED] SUMPRODUCT with non-ordered data

Post by AndyNoCo »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SUMPRODUCT with non-ordered data

Post by eremmel »

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.
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply