OFFSET formula working within a COUNTIF but not within a SUMPRODUCT

[gardnertoo]

On a spreadhseet running in Excel 2007, I have the following formula in cell D7, where it works just fine:

=COUNTIF(OFFSET($S$2112:$S$2211,,(ROW()-ROW($D$6))*4),"OPEN")

It looks at a column of OPEN and CLOSE texts and counts the number of OPEN, straightforward enough. So I took the OFFSET portion of that formula and used it inside the following formula in cell E7, where it throws a #VALUE! Error. I'm looking for the number of times an OPEN in column S, W, AA, etc (every fourth column) resulted in a MISSED in column T, X, AB, etc.

=SUMPRODUCT(--(OFFSET($S$2112:$S$2211,,(ROW()-ROW($D$6))*4)="OPEN"),--(OFFSET($T$2112:$T$2211,,(ROW()-ROW($D$6))*4)="MISSED"))

The Evaluate Formula box shows that the results of the two OFFSET formulas are the problem. Even though it correctly resolves the ROW()-ROW($D$6) portion to a result of 1, and even though that same formula worked in the other cell. Inside the SUMPRODUCT formula it is resolving the OFFSET as ……{#VALUE!}=”OPEN”……
Adding to my confusion, if I replace the string ROW()-ROW($D$6) with a hard-coded “1”, the formula works as it should; if I replace it with another formula whose result is one, for example “7-6”, this also works. (Proving that my SUMPRODUCT is correctly built) It isn’t a number-of-parenthesis or too-many-nested-formulas problem either, since SQRT(1) also works, as does the ridiculous SQRT(COS(RADIANS(90))) just to get several layers of depth. It seems that the formula only fails when it includes either the ROW() or COLUMN() functions, even though those functions do resolve correctly. I have a workaround, moving the (ROW()-ROW($D$6))*4) portion to a helper cell and referring to that cell in the formula, but I want to know WHY DID I HAVE TO DO THAT?

 

[Jonmo1]

I'm not sure about the "Why" part of it, but here's a solution..

=SUMPRODUCT(--(OFFSET($S$2112:$S$2211,,ROWS(A$1:A1)*4)="OPEN"),--(OFFSET($T$2112:$T$2211,,ROWS(A$1:A1)*4)="MISSED"))

 

[Jonmo1]

Still don't understand the "Why"
But here is a significant difference between your formula and the one I posted when using the formula evaluator..
Your formula, the Row()-ROW($D$6)*4 get's evaluated with {} around it..
{7}-{6}*4
{1}*4
{4}

My formula does not get evaluated with the {} around ROWS(A$1:A1)*4
1*4
4

I don't know why yours does that and mine doesn't, nor do I know what it's significance is..
But I know it means something, and it is significant to the formula..
LOL..

 

[Marcelo Branco]

I think this also works

Change this
(ROW()-ROW($D$6))*4

to
SUM(ROW()-ROW($D$6))*4

It seems that OFFSET doesn't like {1} but likes 1...

M.

 

[Jonmo1]

I think the difference between Row and Rows is that Row can be used to return an array of numbers, each row# in the reference.
Row(A1:A3) = {1,2,3}

While Rows only returns a single value, the total number of rows within the referenced range.
Rows(A1:A3) = 3

Sumproduct being the array formula that it is, assumes you want to return the ARRAY of each row in the ROW() formula, so it returns it as an array {7}
The rows function, cannot do that, so it just returns the single number, not as an array.

And then as Marcelo said, Offset doesn't like using an array {4}, it just wants a single number 4.

 

[Marcelo Branco]

Sumproduct being the array formula that it is, assumes you want to return the ARRAY of each row in the ROW() formula, so it returns it as an array {7}
The rows function, cannot do that, so it just returns the single number, not as an array.

This is the important point!

M.

 

[Jonmo1]

This is the important point!

M.

Yep, took a bit of time to wrap my head around why it was doing that..

 

[Marcelo Branco]

Jonmo,

See this.

Few days ago someone wanted a diagonal Sumproduct, something like this

A B C D

10	10	1	2	3
40	20	1	2
100	30	1

<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>
</TBODY>

A1 = B1*C1 (zero columns to right) or 10*1 = 10
A2 = B1*D1 (one column to right) or 10*2 + B2*C2 (zero columns to right) or 20*1 = 40
A3 = B1*E1 (two columns to right) or 10*3 + B2*D2 (one column to right) or 20*2 + B3*C3 (zero columns to right) or 30*1 = 100

and so on

I was trying to create a formula in A1, copied down to A2, A3..., like
=SUMPRODUCT(B$1:B1,OFFSET(C1,-(ROW(C1)-ROW(C$1:C1)),ROW(C1)-ROW(C$1:C1)))
but it was not working, ie, returning 0

Then, by luck , i decided to wrap the OFFSET with N
=SUMPRODUCT(B$1:B1,N(OFFSET(C1,-(ROW(C1)-ROW(C$1:C1)),ROW(C1)-ROW(C$1:C1))))
, miracle!, it worked like a charm!!!

Anyone knows exactly what N does in this case?

M.

 

[gardnertoo]

Jonmo, Marcelo: thank you for the explanation! I appreciate you taking the time to puzzle over my problem.

 

[Jonmo1]

Jonmo, Marcelo: thank you for the explanation! I appreciate you taking the time to puzzle over my problem.

Glad to help, thanks for the feedback.