Row(n:m) not evaluating to an array when placed inside GetPivotData() array formula

[Richie_P]

Hi all,

I couldn't figure out the syntax to use to specify that all the keywords must be present in search results, so I may have missed a past topic dealing with this. If so, sorry about that.

My problem is as follows:

When I use (for instance) Row(1:5) inside a Sum array formula:

{=SUM(Row(1:5))}

I get a result of 15.

However, when I try using Row(1:5) inside a GetPivotData array formula:

{=GETPIVOTDATA("Supporters",$A$4,"PaymentCount",ROW(1:5))}

(intending to get the total number of Supporters with a PaymentCount between 1 and 5), Row(1:5) evaluates to 1 instead of {1,2,3,4,5} and all I get is the total number of Supporters with a PaymentCount of 1.

I've slightly simplified this - in the full version I'm using Indirect within Row() to dynamically generate the range, but I've tested when reduced to the case stated above and the problem persists. What I'm actually after is the total number of Supporters with a PaymentCount > X, where X varies with the row of the table I'm building, so the Indirect changes the array I want out from {1,2,3,4,5} to {2,3,4,5} to {3,4,5}, etc.

 

[DonkeyOte]

Without knowing the finer points of your setup you should find that:

=SUM(GETPIVOTDATA("Supporters",$A$4,"PaymentCount",ROW(1:5)))
confirmed with Enter as normal

should work... note it is not an Array.

the use of INDIRECT to build the ROW array will make the above volatile - I would say if you can you might consider using INDEX instead, eg:

ROW(A$1:INDEX(A:A,number))

 

[Richie_P]

Thanks, DonkeyOte - that seems to work perfectly. From the quick test I've done, both INDIRECT and INDEX seem to provide the correct result when the entire formula (your SUM version) is entered as an array formula.

I'll try a more robust test when I'm back at work tomorrow.

Your help was much appreciated.

 

[DonkeyOte]

Sorry yes you're quite right - Array entry - does help to test!