Excel VBA for countifs with one criteria dependent on another criteria

Question

I'm doing some coding for a basketball team where I have thousands of lines of data which contain 2 columns that represent X, Y coordinates where shots have been taken on a half court. I need to be able to count the number of coordinates in each area of the court. For example; calculating shots taken in the paint is easy because the paint is a rectangle area, I.e using countifs(X:X, AND(X>= 120, X<= 320), Y:Y, AND(Y<=280).

But I need to calculate shots taken from the right wing and left wing, which the coach defines as taking a straight line from underneath the basket through the top right corner of the key, and a second straight line from underneath the basket through the top left corner of the key. These lines are diagonal, and I'm having trouble figuring out how to code it correctly in VBA, since the Y coordinate value is dependent on the X coordinate value. let's say in this case, the linear equation of the line is Y=-2.48X +500, and so I need the Countif to count all the shots taken where the Y Coordinate is < 500 - (2.48 * X coordinate).

Anyone have any ideas? I'm stuck here, conceptually it would look like this but I know this doesn't work!

=countifs("Y:Y", "<500 - 2.48 * (""X:X"")")

**realize my =countifs only contains 1 criteria, using Countifs because there are other criteria I need to use as well but omitting them for ease of exercise. Other criteria I'm using include stuff like checking a column to see if the columns value = Team Name, so that it only counts rows where Team A takes a shot, or another column which indicates shot made or missed with a + or -

Answer 1

You are going to require an array formula¹ or SUMPRODUCT, 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.

In a cell as a standard formula,

=SUMPRODUCT(--(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))<500-2.48*X$2:INDEX(X:X, MATCH(1E+99,Y:Y ))))

The MATCH function 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).

In a cell as an array formula¹,

=SUM(IF(X$2:INDEX(X:X, MATCH(1E+99,Y:Y )), --(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))<497.52), --(Y$2:INDEX(Y:Y, MATCH(1E+99,Y:Y ))<500)))

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. 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 Guidelines and examples of array formulas for more information.}