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
Maybe:
=SUMPRODUCT((A6:D6>B6:E6)+0)
Assuming too much and qualifying too much are two faces of the same problem.
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.
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
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.
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others