[libreoffice-users] SumIF / SumProduct question
I have a spread sheet I use to manage a soccer league. This shows me of the registered players who is on Dk Blue team. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $O$2:$O$131=x) This shows me the team strength based on assigned values of the players. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) I would like to do something like this (this will not work): =SUMIF($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) If 02-0131 is the Spring registration column. If there is a x there then I know they are playing for the year. I would like to say, If they are registered for spring then add up the points in AW2-131 if they are on TeamName. Any ideas appreciated. Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] SumIF / SumProduct question
Just an off-the-top-of-my-head stab at it, but can't you do something like: =SUMIF(($O$2:$O$131=x AND $AU$2:$AU$131=Dk Blue), $AW$2:$AW$131) ? Paul On Tue, 29 Jul 2014 16:34:00 -0500 Wade Smart wadesm...@gmail.com wrote: I have a spread sheet I use to manage a soccer league. This shows me of the registered players who is on Dk Blue team. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $O$2:$O$131=x) This shows me the team strength based on assigned values of the players. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) I would like to do something like this (this will not work): =SUMIF($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) If 02-0131 is the Spring registration column. If there is a x there then I know they are playing for the year. I would like to say, If they are registered for spring then add up the points in AW2-131 if they are on TeamName. Any ideas appreciated. Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] SumIF / SumProduct question
Gives a Err:508. I dont think it can be text that its comparing. -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Tue, Jul 29, 2014 at 4:42 PM, Paul paulste...@afrihost.co.za wrote: Just an off-the-top-of-my-head stab at it, but can't you do something like: =SUMIF(($O$2:$O$131=x AND $AU$2:$AU$131=Dk Blue), $AW$2:$AW$131) ? Paul On Tue, 29 Jul 2014 16:34:00 -0500 Wade Smart wadesm...@gmail.com wrote: I have a spread sheet I use to manage a soccer league. This shows me of the registered players who is on Dk Blue team. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $O$2:$O$131=x) This shows me the team strength based on assigned values of the players. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) I would like to do something like this (this will not work): =SUMIF($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) If 02-0131 is the Spring registration column. If there is a x there then I know they are playing for the year. I would like to say, If they are registered for spring then add up the points in AW2-131 if they are on TeamName. Any ideas appreciated. Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] SumIF / SumProduct question
Yeah, you're right, forgot how SUMIF works for a moment... It's easy with an intermediate column, but one-shot... In fact, I'm not sure that it isn't *supposed* to be done with intermediate columns. At least, the only way I can see that it could be done without them would be either some sort of temporary column, or by defining the return value of a range, neither of which are really the purpose of spreadsheets, as I see it. Maybe Brian has some smart ideas. Paul On Tue, 29 Jul 2014 16:50:58 -0500 Wade Smart wadesm...@gmail.com wrote: Gives a Err:508. I dont think it can be text that its comparing. -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Tue, Jul 29, 2014 at 4:42 PM, Paul paulste...@afrihost.co.za wrote: Just an off-the-top-of-my-head stab at it, but can't you do something like: =SUMIF(($O$2:$O$131=x AND $AU$2:$AU$131=Dk Blue), $AW$2:$AW$131) ? Paul On Tue, 29 Jul 2014 16:34:00 -0500 Wade Smart wadesm...@gmail.com wrote: I have a spread sheet I use to manage a soccer league. This shows me of the registered players who is on Dk Blue team. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $O$2:$O$131=x) This shows me the team strength based on assigned values of the players. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) I would like to do something like this (this will not work): =SUMIF($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) If 02-0131 is the Spring registration column. If there is a x there then I know they are playing for the year. I would like to say, If they are registered for spring then add up the points in AW2-131 if they are on TeamName. Any ideas appreciated. Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] SumIF / SumProduct question
I guess I should have just tried this: =SUMPRODUCT($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AX$2:$AX$131) I thought I had and it did not work but, it does. :D Thanks -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Tue, Jul 29, 2014 at 5:16 PM, Paul paulste...@afrihost.co.za wrote: Yeah, you're right, forgot how SUMIF works for a moment... It's easy with an intermediate column, but one-shot... In fact, I'm not sure that it isn't *supposed* to be done with intermediate columns. At least, the only way I can see that it could be done without them would be either some sort of temporary column, or by defining the return value of a range, neither of which are really the purpose of spreadsheets, as I see it. Maybe Brian has some smart ideas. Paul On Tue, 29 Jul 2014 16:50:58 -0500 Wade Smart wadesm...@gmail.com wrote: Gives a Err:508. I dont think it can be text that its comparing. -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Tue, Jul 29, 2014 at 4:42 PM, Paul paulste...@afrihost.co.za wrote: Just an off-the-top-of-my-head stab at it, but can't you do something like: =SUMIF(($O$2:$O$131=x AND $AU$2:$AU$131=Dk Blue), $AW$2:$AW$131) ? Paul On Tue, 29 Jul 2014 16:34:00 -0500 Wade Smart wadesm...@gmail.com wrote: I have a spread sheet I use to manage a soccer league. This shows me of the registered players who is on Dk Blue team. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $O$2:$O$131=x) This shows me the team strength based on assigned values of the players. =SUMPRODUCT($AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) I would like to do something like this (this will not work): =SUMIF($O$2:$O$131=x,$AU$2:$AU$131=Dk Blue, $AW$2:$AW$131) If 02-0131 is the Spring registration column. If there is a x there then I know they are playing for the year. I would like to say, If they are registered for spring then add up the points in AW2-131 if they are on TeamName. Any ideas appreciated. Wade -- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
