Countifs / sumproduct...

[poolio]

I think I am looking for a relatively simple countif output, but stuggling to type it up...

Basically I've got a load of football results in neighbouring columns: Home score | Away score | Home score | Away score | Home score | Away score | etc... (B6 | C6 | D6 | E6 etc...)

I want to report in a column at the end the number of times the Home Score is Greater than the Away Score (and Vice Versa)...

My Header Row (A5:D5 above the scores) are numbered alternating 5 | 6 | 5 | 6..

So I'm trying to input a formula which looks to see if the values in the columns headed 5 are greater than their neighbours, headed 6...

Does this need an Array Formula - something like this??

{COUNTIF(A6:D6,((A5:D5=5)>(A5:D5=6)))} ???

JP

 

[Marcelo Branco]

My Header Row (A5:D5 above the scores) are numbered alternating 5 | 6 | 5 | 6..

So I'm trying to input a formula which looks to see if the values in the columns headed 5 are greater than their neighbours, headed 6...

Maybe...
=SUMPRODUCT(--(A6:C6>B6:D6),--(A5:C5=5))

M.

 

[Aladin Akyurek]

Maybe:

=SUMPRODUCT((A6:D6>B6:E6)+0)

 

[a massive zebra]

Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.

 

[poolio]

Thanks guys...
Marcelo's solution works!
I think Aladin's method fails as it compares the Home with the Away Result (as I wanted), but then also compares that Away results with the next Home result... thus counting some noe results.
e.g. There are 2 results here, 3-2 and 1-0, thus there are 2 "home wins".
Marco's system would see; 3 > 2, 2 > 1 and 1> 0, i.e. 3 "home wins".
5 | 6 | 5 | 6
3 | 2 | 1 | 0

 

[Aladin Akyurek]

Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.

1. A relational comparison (with >, <, so on) delivers either a TRUE or a FALSE.

2. SUMPRODUCT works (needs) only with numbers.

3. An operator like + acts as a coercer.

4. TRUE+0 ==> 1; FALSE+0 ==> 0. (By the way, TRUE is 1 and FALSE is 0 in Excel.)

5. Hence +0. That is, all comparison results are transformed (coerced) into 1's and 0's.

6. SUMPRODUCT sums those 1's and 0's. The sum that obtains is effectively the count we are looking for.

 

[Aladin Akyurek]

Thanks guys...
Marcelo's solution works!
I think Aladin's method fails as it compares the Home with the Away Result (as I wanted), but then also compares that Away results with the next Home result... thus counting some noe results.
e.g. There are 2 results here, 3-2 and 1-0, thus there are 2 "home wins".
Marco's system would see; 3 > 2, 2 > 1 and 1> 0, i.e. 3 "home wins".
5 | 6 | 5 | 6
3 | 2 | 1 | 0

In that case, we need to use the 5 and 6 codings.

 

[Marcelo Branco]

Thanks guys...
Marcelo's solution works!

You are welcome. Thanks for the feedback.

M.

 

[a massive zebra]

1. A relational comparison (with >, <, so on) delivers either a TRUE or a FALSE.

2. SUMPRODUCT works (needs) only with numbers.

3. An operator like + acts as a coercer.

4. TRUE+0 ==> 1; FALSE+0 ==> 0. (By the way, TRUE is 1 and FALSE is 0 in Excel.)

5. Hence +0. That is, all comparison results are transformed (coerced) into 1's and 0's.

6. SUMPRODUCT sums those 1's and 0's. The sum that obtains is effectively the count we are looking for.

Thanks very much for the explanation.

 

[Aladin Akyurek]

Thanks very much for the explanation.

You are welcome.