# Comparing data in rows

#### ebandme

##### New Member
I have data in the following table:
1 2 3 4 5 6
a 50 53 73 72 62.5 33
b 63 110 68.5 82 30 50.5
c 67 40.5 44 66.5 49 69
d 95.5 95.5 52 124 76.5 43.5
e 65 85 45 86 31 35
f 64 112 75 90 50 15

each column is a score for a particular week for that particular row. What I am trying to do is find a formula or way to calculate how many times row 'a' score was greater than row 'b' score, row 'a' greater than 'c', and so on.
If this needs a sepreate table then fine but my main issue is comparing two rows of data and counting how many times one row is larger than the other.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### erik.van.geit

##### MrExcel MVP
Hi, ebandme
Welcome to the Board !!!!!

try something like this
A    B    C    D    E    F    G  H           I
1 50   53   73   72   62,5 33                  ROW
2 63   110  68,5 82   30   50,5                2
3 67   40,5 44   66,5 49   69      higher than 4
4 95,5 95,5 52   124  76,5 43,5
5 65   85   45   86   31   35      result      3
6 64   112  75   90   50   15

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
I5    =SUMPRODUCT(--(OFFSET(\$A\$1:\$F\$6,I2-1,,1,)>OFFSET(\$A\$1:\$F\$6,I3-1,,1,)))

[Table-It] version 06 by Erik Van Geit``````
TIP: next time you post a table use Table-It (see my signature) or colo's HTMLmaker (see bottom page)

kind regards,
Erik

#### ebandme

##### New Member
I appreciate your help but I am still having difficulty.
I am looking for a one number answer that tells me how many times row 1 was greater than row 2.
if there is a formula that does: count how many times row 1's score was greater than row 2's score.

I tried using your example but I received an error - is there a way to use count of sumif formula?

#### erik.van.geit

##### MrExcel MVP
Hi,

it works nicely for me, exactly what you want
so let's find out what your problem might be

you didn't tell in which cells the table is
perhaps you reversed the columnlabels and rownumbers in your example ? therefore I used a table starting in A1

so care to put your table in the same range
fill in a number in I2 and I4
the numbers represent the "relative row" in your table

as you can see in the example I2 has "2" and I3 has "4"
the result is "3": there are indeed 3 values in "row"2 higher than "row"4

I forgot to change the values again to "1" and "2" after testing
so to get the result comparing "row"1 and "row"2, just change I2 and I3 accordingly

best regards,
Erik

thanks - got it.

Replies
9
Views
60
Replies
3
Views
45
Replies
1
Views
168
Replies
2
Views
43
Replies
2
Views
66