Array Formula With Subtotals

[kcc]

This is a simplified example of what I'm trying to do.

Assume A1 to F100 has data. I want an array formula that
will calc something like
=SUM(IF((A1:A100>0)*(B1:B100<0),A1:A100-xxxx(C1:E100),B1:B100-xxxx(C1:F100)))
or similar using SUMPRODUCT.
Basically for each row calc
if(and(A1>0, B1<0),A1-sum(C1:E1),B1-sum(C1:F1))
and then sum the rows. Sum used where xxxx is doesn't
work because it needs to return an array, not the sum of
all rows and columns. xxxx needs to sum the columns,
but return a separate answer for each row.

The real problem has lots of columns and lots of groupings
so adding subtotal columns is not practical, nor is anything
identifying each column separately.

Thanks, kcc

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF((A1:A100>0)*(B1:B100<0),A1:A100-SUBTOTAL(9,OFFSET(C1:E100,ROW(C1:
E100)-ROW(C1),0,1)),B1:B100-SUBTOTAL(9,OFFSET(C1:F100,ROW(C1:F100)-ROW(C1
),0,1))))

Hope this helps!

 

[kcc]

I can tell what it's doing, but I'm having trouble telling why
it's doing it. Specifically why does the SUBTOTAL sum the rows
but not the columns the way SUM or SUBTOTAL w/o OFFSET would.

Regardless, it works perfectly.
Thanks
kcc

 

[Domenic]

I can tell what it's doing, but I'm having trouble telling why
it's doing it. Specifically why does the SUBTOTAL sum the rows
but not the columns the way SUM or SUBTOTAL w/o OFFSET would.

The key is that OFFSET returns an array of references/subranges for
SUBTOTAL to sum. For example, if we look at the first OFFSET part of
the formula...

OFFSET(C1:E100,ROW(C1:E100)-ROW(C1),0,1)

....here's how it breaks down...

ROW(C1:E100) evaluates to:

1
2
3
4
5
..
..
..

100

ROW(C1) returns a single result:

1

ROW(C1:E100)-ROW(C1) evaluates to:

0
1
2
3
4
..
..
..

99

This array of numbers is used for the second argument of the OFFSET
function and returns the following array of references/subranges...

OFFSET(C1:E100,0,0,1) ---> C1:E1

OFFSET(C1:E100,1,0,1) ---> C2:E2

OFFSET(C1:E100,2,0,1) ---> C3:E3

OFFSET(C1:E100,3,0,1) ---> C4:E4

..
..
..

OFFSET(C1:E100,99,0,1) ---> C100:E100

This array of references/subranges is then fed to the SUBTOTAL function,
which returns an array of values. In turn, this array of values is
subtracted from the array of values provided by A1:A100.

Hope this helps!

 

[kcc]

Thanks,
Interesting how an array of subranges is different from a 2 dimensional
array.
I guess it's like nested arrays in a lot of programming languages.
kcc