[Solved] SUMPRODUCT with non-ordered data

Discuss the spreadsheet application

[Solved] SUMPRODUCT with non-ordered data

Postby AndyNoCo » Thu Oct 05, 2017 12:12 am

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 16 times
Last edited by AndyNoCo on Fri Oct 06, 2017 3:44 am, edited 1 time in total.
OpenOffice 4.1.3, macOS Sierra 10.12.6
AndyNoCo
 
Posts: 14
Joined: Wed Sep 27, 2017 9:43 pm

Re: SUMPRODUCT with non-ordered data

Postby FJCC » Thu Oct 05, 2017 12:25 am

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6160
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Postby AndyNoCo » Thu Oct 05, 2017 2:10 am

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.
OpenOffice 4.1.3, macOS Sierra 10.12.6
AndyNoCo
 
Posts: 14
Joined: Wed Sep 27, 2017 9:43 pm

Re: SUMPRODUCT with non-ordered data

Postby FJCC » Thu Oct 05, 2017 2:39 am

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 5 times
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6160
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUMPRODUCT with non-ordered data

Postby acknak » Thu Oct 05, 2017 2:59 am

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 9 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22685
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: SUMPRODUCT with non-ordered data

Postby AndyNoCo » Thu Oct 05, 2017 4:54 am

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.
OpenOffice 4.1.3, macOS Sierra 10.12.6
AndyNoCo
 
Posts: 14
Joined: Wed Sep 27, 2017 9:43 pm

Re: SUMPRODUCT with non-ordered data

Postby AndyNoCo » Fri Oct 06, 2017 3:43 am

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.
OpenOffice 4.1.3, macOS Sierra 10.12.6
AndyNoCo
 
Posts: 14
Joined: Wed Sep 27, 2017 9:43 pm

Re: [SOLVED] SUMPRODUCT with non-ordered data

Postby acknak » Fri Oct 06, 2017 5:45 am

Good work!
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22685
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [SOLVED] SUMPRODUCT with non-ordered data

Postby AndyNoCo » Fri Oct 06, 2017 6:37 am

I'm just waiting for something I missed to jump up and bite me in the keister. ;-) But it looks good for now...
OpenOffice 4.1.3, macOS Sierra 10.12.6
AndyNoCo
 
Posts: 14
Joined: Wed Sep 27, 2017 9:43 pm

Re: SUMPRODUCT with non-ordered data

Postby eremmel » Fri Oct 06, 2017 9:16 am

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 940
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Calc

Who is online

Users browsing this forum: No registered users and 37 guests