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

[ajw1982]

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

[benishiryo]

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. =)

[ajw1982]

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.

[Fotis1991]

.

..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?

[ajw1982]

.

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

[Fotis1991]

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))

[ajw1982]

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.

[Fotis1991]

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

[ajw1982]

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).