+ Reply to Thread
Results 1 to 9 of 9

Using SUMPRODUCT... Do I need to add SUM to this formula as well?

  1. #1
    Registered User
    Join Date
    02-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    14

    Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Hi,

    Already asked a few questions about this spreadsheet I'm creating and this forum has been a lifesaver. Thanks so much all!

    The question I now have:

    I have a sheet of football results that I am analysing in detail be team. My result sheet is E0 12-13 (see attached sheet). On my analysis sheet I now want to work out the number of instances of 0-0, 0-1, 0-2 etc. results in the second half.

    As you can see I've done this fine for the full time result and the half time result as it's easier as E0 12-13 contains columns for Full Time Home Goals (E), Full Time Away Goals (F), Half Time Away Goals (H), Half Time Away Goals (I).

    Let's take 0-0 as the example. To create the instances that the second half (excluding what happened in the first half) was 0-0 I have tried the following formla:

    =SUMPRODUCT(('E0 12-13'!$C$1:$C$1000=$A$4)*('E0 12-13'!$E$1:$E$1000-'E0 12-13'!$H$1:$H$1000=0)*('E0 12-13'!$F$1:$F$1000-'E0 12-13'!$H$1:$H$1000=0))

    Essentially trying to get Full Time minus Half Time goals to leave me with the number of goals in the second half for each team, and then to search when those instances were both 0.

    However the formula is returning and error "VALUE". Any ideas what I'm doing wrong?



    Thanks,
    Alex
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    hi Alex. a little lazy to vet through the whole thing, but you could try changing row 1 to row 2 in your formula. it gets a VALUE error because your row 1 contains headings. so if you take E1 - H1, it is taking "FTHG" - "HTHG".
    =SUMPRODUCT(('E0 12-13'!$C$2:$C$1000=$A$4)*('E0 12-13'!$E$2:$E$1000-'E0 12-13'!$H$2:$H$1000=0)*('E0 12-13'!$F$2:$F$1000-'E0 12-13'!$H$2:$H$1000=0))

    it would be good if you can give us the desired results, so it makes it easier for us. =)
    Last edited by benishiryo; 02-15-2013 at 06:06 AM. Reason: spotted mistake

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    02-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Thanks for the quick response. Your amend does have an impact. Beneath 0-0 it now yields the result "TRUE".

    In fact, I have done the manual analysis and the result I am hoping to see is "2" (i.e. there have basically been 2 games where no goals have been scored in the 2nd half, i.e. second half-result was 0-0).

    Hope that helps.

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    .
    ..In fact, I have done the manual analysis and the result I am hoping to see is "2" (i.e. there have basically been 2 games where no goals have been scored in the 2nd half, i.e. second half-result was 0-0).
    Which are these 2 rows, Alex?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    02-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Quote Originally Posted by Fotis1991 View Post
    .

    Which are these 2 rows, Alex?
    Hello again! Yes, I'm back

    It's rows 2 (which stayed 0-0 throughout the match so obviously 2nd half is 0-0) and also row 218 (was 0-2 at HT and 0-2 at FT, therefore second half "result" was 0-0).

    Hope that clarifies.

    Alex

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    In BS2 of your first sheet put this and copy down.

    =IF(AND(E2=H2,F2=I2),1,"")

    Then use this formula to get your result

    =SUMPRODUCT(('E0 12-13'!$C$2:$C$1000=$A$4)*('E0 12-13'!BS2:BS1000=1))

  7. #7
    Registered User
    Join Date
    02-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Quote Originally Posted by Fotis1991 View Post
    In BS2 of your first sheet put this and copy down.

    =IF(AND(E2=H2,F2=I2),1,"")

    Then use this formula to get your result

    =SUMPRODUCT(('E0 12-13'!$C$2:$C$1000=$A$4)*('E0 12-13'!BS2:BS1000=1))
    Thanks. That does actually work and gives me the result for the 0-0 results. Now if I wanted to elaborate that formula so in the next column I could work out 1-0, 2-0, 3-0 etc. in the second half, would that be possible?

    Essentially I was trying to use SUMPRODUCT to do something like E-H="1" * F-I="0" (in the case of 1-0 in second half), but cannot get this to work.

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Same way. And as you work in Excel2007, we'll replace the SUMPRODUCT, for more efficient. We use Countifs.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Using SUMPRODUCT... Do I need to add SUM to this formula as well?

    Quote Originally Posted by Fotis1991 View Post
    Same way. And as you work in Excel2007, we'll replace the SUMPRODUCT, for more efficient. We use Countifs.
    Thank you... Thank you...

    So kind of you. You've massively helped me out again. My masterpiece is taking shape thanks to you.

    PS I looked at your formula for yesterday and I think I will use it as it's a good way to quickly check and analyse things on a case by case basis (for teams recent form).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1