Combining COUNTIF, SUMPRODUCT, and SUBTOTAL to count the number of entries in the difference between two conditional arrays without helper columns

Here's a seemingly simple problem which has left me stumped. Let's take the following data matrix:

A   B   C   D       E   F   G
0   0   0   1000    10  7   5
0   0       2000    8   6   
0   1   0   3000    9   9   7
0   0       4000    10  10  
0   0       5000    3   10  7
6   6       6000    10  10  
1   2   0   7000    10  10  8
3   5       8000    3       
3           9000    5       
0   0   0   10000   7   6   7
9   5       11000   8   8   
3   2   4   12000   3   4   3

My goals are:

1. To average rows across certain columns that have complete data
2. To take the difference between the average of two sets of columns in step 1.
3. To count the number of entries in step 2. that fulfill a certain criteria
4. To do all of the above without creating any helper columns (within reason!).

Let's see how this would play out in practice.

Step 1. Average rows across certain columns that have complete data

We need to average each row in columns A:C and also in E:G, so long as there are entries in both A:C and E:G.

We could envision two new columns with averages for each row in columns A:C (see H below) and E:G (see I), but only for rows with full data in A:C and E:G (e.g., rows 1, 3, 7, 10, and 12 below) Remember, the point is to do this without relying on helper columns! They are used here for demonstration purposes:

H   I
0   7.3

0.3 8.33



1   9.3


0   6.7

3   3.3

Step 2. Take the difference between the average of two sets of columns

We could envision another column that includes the result of subtracting each row in column I from each row in column H:

J
7.3

8



8.3


6.7

0.3

Step 3. Count the number of entries in step 2. that fulfill a certain criteria

We want to count values > 8 as well as values < 1. In the current example, the answer is 2 because there are four entries > 8 (e.g., J7, 8.3) and one entry less than 1 (e.g., J12, 0.3).

To achieve this without relying on helper columns, I have tried to make use of SUMPRODUCT and SUBTOTAL, but to no avail. The simplest solution may be structured as follows:

=SUMPRODUCT(--((*AVERAGE OF COLUMNS A:C*)-(*AVERAGE OF COLUMNS E:G*) > 7),--((*AVERAGE OF COLUMNS A:C*)-(*AVERAGE OF COLUMNS E:G*) < 1))

This may look something like the following (with the <1 condition omitted for brevity):

=SUMPRODUCT((IF(SUBTOTAL(2,OFFSET(C1,ROW(A1:A12)-ROW(C1),0,1,COLUMNS(A1:C1)))=COLUMNS(A1:C1),IF(SUBTOTAL(2,OFFSET(E1,ROW(E1:E12)-ROW(E1),0,1,COLUMNS(E1:G1)))=COLUMNS(E1:G1),SUBTOTAL(1,OFFSET(A1,ROW(A1:A12)-ROW(A1),0,1,COLUMNS(A1:C1))))))-(IF(SUBTOTAL(2,OFFSET(C1,ROW(A1:A12)-ROW(C1),0,1,COLUMNS(A1:C1)))=COLUMNS(A1:C1),IF(SUBTOTAL(2,OFFSET(E1,ROW(E1:E12)-ROW(E1),0,1,COLUMNS(E1:G1)))=COLUMNS(E1:G1),SUBTOTAL(1,OFFSET(A1,ROW(E1:E12)-ROW(E1),0,1,COLUMNS(E1:G1)))))) > 7)

The code produces a value of 0 (because the IF formula for calculating the averages for rows A:C and E:G each results in 0). My understanding is that `SUMPRODUCT easily handles subtraction between two columns while including multiple count criteria, but perhaps you envision another method. I would love to hear your thoughts.