How to OFFSET from a dynamic named range and use that range in SUMPRODUCT?

Question

Situation:

I am getting #Value! when trying to pass the OFFSET of a dynamic named range to SUMPRODUCT.

Setup:

I have the following data in range A2:B4 of Sheet1.

| TextA | 1 |
|-------|---|
| TextA | 2 |
|-------|---|
| TextB | 3 |

I have created a dynamic named range, textRange, of the values in column A with the formula:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1048576),1)

As shown in image:

Note: textRange will pick up all required rows. It can be assumed to be pulled from a continuously populated range i.e. textRange covers all the rows required.

Aim:

I now want to Offset from this, using the Column function, to get the same rows 1 column across i.e. B2:B4; and then sum the values in this range if the corresponding text in column A ends with "A".

The expected result would be 3.

Process:

1) I am using the following formula to construct the offset range:

OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1)

The offset range could be any column after A, hence my use of Column function so can drag formula across to return range of interest.

2) I then pass this Offset range to SUMPRODUCT and sum it's value if the corresponding Column A row has "A" as it's last letter i.e.

=SUMPRODUCT(OFFSET(A2,0,COLUMN(B1)-1,COUNTA(textRange),1),--(RIGHT(textRange,1)="A"))

Outcome:

The expected result would be 3 but is currently #Value!

Question:

What I am doing wrong? I am guessing it is because of how I am passing the range.

Requested solution:

I am open to any other way of achieving the same result. However, the formula must update the Offset for the dynamic range, when dragged across columns, and must perform the conditional sum on the new set of rows.

Reference:

https://chandoo.org/forum/threads/using-offset-function-with-sumproduct.960/

Answer 1

The #Value! error seems to be coming from the fact that Column(B1) produces a single-cell array as you can see if you trace through it with Evaluate Formula

It's certainly a strange one but I'm guessing that OFFSET would then try to return an array of ranges, which can only be dealt with by a few functions - N(), SUBTOTAL and INDEX.

You can fix it by SUM-ing the array like this

=SUMPRODUCT(OFFSET($A2,0,SUM(COLUMN(B1)-1),COUNTA(TextRange),1),--(RIGHT(TextRange,1)="A"))

or wrapping the OFFSET in an INDEX

=SUMPRODUCT(INDEX(OFFSET($A2,0,COLUMN(B1)-1,COUNTA(TextRange),1),0,1),--(RIGHT(TextRange,1)="A"))