Counting cells in Google Sheets where one row exceeds another

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need help with a comparison function. Here’s what I want to do:

Given two rows of data, I need to count how many cells in the first row are greater than the corresponding cells in the second row.

For instance:

Row 1: 5 8 3 1
Row 2: 4 7 3 2

The function should return 2 because two cells in Row 1 (5 and 8) are greater than their counterparts in Row 2.

I’ve tried creating a custom JavaScript function, but it’s not working as expected. Here’s my attempt:

function compareRows(row1, row2) {
  let count = 0;
  for (let i = 0; i < row1.length; i++) {
    if (row1[i] > row2[i]) {
      count++;
    }
  }
  return count;
}

Can someone help me figure out what I’m doing wrong? I’m not sure if I’m handling the cell references correctly in Google Sheets. Any advice would be great!

2

I’ve dealt with similar comparison tasks in Google Sheets before, and I think I can offer some insight. Your JavaScript function looks correct, but the issue might be in how you’re calling it from within Sheets.

For this kind of operation, you don’t actually need a custom function. Google Sheets has built-in array formulas that can handle this comparison efficiently. Here’s a formula that should work for you:

=SUMPRODUCT(A1:D1>A2:D2)

This formula assumes your data is in cells A1:D1 for the first row and A2:D2 for the second row. It compares each cell in the first range to the corresponding cell in the second range, then sums up the TRUE results (which are treated as 1 in the calculation).

If you need to make this more dynamic, you could use whole column references like A:A>B:B, but be cautious as this might slow down your sheet if you have a lot of data. Hope this helps solve your problem!

3

I’ve encountered this type of comparison challenge before, and while your JavaScript approach is on the right track, there’s a simpler solution using Google Sheets’ native functions. Try this formula:

=ARRAYFORMULA(SUM(IF(A1:D1>A2:D2,1,0)))

This formula compares the cells in the first row (A1:D1) to those in the second row (A2:D2). It returns 1 for each cell where the first row’s value exceeds the second, then sums these results. The ARRAYFORMULA ensures it works across the entire range without needing to be dragged.

Adjust the cell ranges as needed for your specific sheet layout. This method is efficient and doesn’t require custom scripts, making it easier to maintain and share with others.

4

hey mate, i think i got a solution for ya. try this formula:

=COUNTIF(A1:D1,“>”&A2:D2)

it compares each cell in row 1 to the one below it and counts when its bigger. just change A1:D1 and A2:D2 to match ur data. no fancy javascript needed!