 <p>You are going to require an array formula¹ or <a href="https://support.office.com/en-us/article/sumproduct-function-4e0bffa7-4291-4635-a61f-6aaa9399e7ff" rel="nofollow">SUMPRODUCT</a>, neither of which handles 'thousands of rows' well due to the logrythmic taxation of cyclic processing. Full column references should be avoided at all costs.</p> <p>In a cell as a standard formula,</p> <pre><code>=SUMPRODUCT(--(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))&lt;500-2.48*X$2:INDEX(X:X, MATCH(1E+99,Y:Y )))) </code></pre> <p>The <a href="https://support.office.com/en-us/article/match-function-0600e189-9f3c-4e4f-98c1-943a0eb427ca" rel="nofollow">MATCH function</a> uses an approximate match for an impossibly high number. This returns the row number of the last number in column Y. It is used to limit both X and Y columns to the extent of the data. The last number in column Y is used in both cases as SUMPRODUCT requires that the ranges be the same size (although not necessarily the same rows).</p> <p>In a cell as an array formula¹,</p> <pre><code>=SUM(IF(X$2:INDEX(X:X, MATCH(1E+99,Y:Y )), --(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))&lt;497.52), --(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))&lt;500))) </code></pre> <hr> <p>¹ <sub>Array formulas need to be finalized with <kbd>Ctrl</kbd>+<kbd>Shift</kbd>+<kbd>Enter↵</kbd>. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See <a href="https://support.office.com/en-ca/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7" rel="nofollow">Guidelines and examples of array formulas</a> for more information.</sub></p>