[Solved] Count of A1<B1 or A2<B2 or A3<B3 ...

[Rhed]

This should be easy. I can quickly figure out the FOREACH/IF statement, but I can't translate that to spreadsheet-ese. I want to go down row by row and compare if the value in one cell is less than the value in another cell. If that condition is true, increment the counter and check the next row.

So, given:

$38.88 | $38.88
$41.40 | $93.56
$92.65 | $104.69
$143.10 | $143.10
$59.40 | $59.45
$59.40 |
$63.50 | $0.00

the answer is 2 (Rows 6 & 7).

I could then change the criteria to A > B and get the answer 3 (Rows 2, 3, & 5). Empty cells (Row 6) are equal to 0.

Thank you in advance.

[robleyd]

SUMPRODUCT() may be a solution; see for example X29 in [Tutorial] The SUMPRODUCT function.

[MrProgrammer]

I can quickly figure out the FOREACH/IF statement, but I can't translate that to spreadsheet-ese.

Spreadsheets do not have FOREACH statements.
Spreadsheets do not have IF statements.
Spreadsheets do not have any statements at all! Instead, they use formulas.
[Tutorial] Ten concepts that every Calc user should know

I want to go down row by row and compare if the value in one cell is less than the value in another cell. If that condition is true, increment the counter and check the next row.

202405060826.ods

(15.08 KiB) Downloaded 56 times

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Rhed]

@MrProgrammer

Sorry, but in [Tutorial] The SUMPRODUCT function which you wrote five years ago, you have a typo in the second paragraph where you say "Let's take a look at an example so you can wee why these values are important."

I also found "equais" in X29 and X30.

There may be others, but I was just skimming for relevant information.

Thanks.

[Rhed]

@robleyd - Thanks so very much for your guidance. I appreciate your assisting me to find a plausible, yet cryptic, solution. You are awesome! A real superstar!

By name alone, I would never have thought to use a function called SUMPRODUCT. Google's AI couldn't equate comparing values in Calc to SUMPRODUCT so searches weren't helpful either. OpenOffice Calc isn't very user-friendly so it is vital to have great people like you to help others.

You have my undying gratitude, sir.

[MrProgrammer]

I would never have thought to use a function called SUMPRODUCT.

SUMPRODUCT is an important function with many uses, as you've seen in the tutorial. I would think that most of the Introduction to Spreadsheets books which one might study would explain it in later chapters. But columns D and F use simple formulas to determine the counts; they just use the < or > operators with the SUM function. That is the technique that I would suggest for beginners.

you have a typo in [the tutorial]

I appreciate your assistance in improving the tutorial. Thank you.