Excel 2010 Using COUNTA As A Dynamic Value In A Formula 1

[PWD]

Good morning I'd like to use a dynamic value for a formula, i.e. I'd like to replace the "409" with something like "COUNTA('AE Reattendances'!A:A)"

=SUMPRODUCT((1/COUNTIF('A&E Reattendances'!$N$2:N409,'AE Reattendances'!$N$2:N409))*('AE Reattendances'!$F$2:F409='Weekly Activity Summary'!K$4))

but just substituting that is invalid and I've got a mental block on how else to formulate the syntax.

I've just tried

=SUMPRODUCT((1/COUNTIF(CONCATENATE("'AE Reattendances'!$N$2:N" & COUNTA('AE Reattendances'!A:A)),CONCATENATE("'AE Reattendances'!$N$2:N" & COUNTA('AE Reattendances'!A:A))))*(CONCATENATE("'AE Reattendances'!$F$2:F" & COUNTA('AE Reattendances'!A:A))='Weekly Activity Summary'!

and although each individual part of the formula shows (including the quote marks)

"'AE Reattendances'!$N$2:N409"

its still not a valid formula.

Many thanks,
D€$

 

[combo]

You can use OFFSET function to create dynamic range that depend on parameters.

combo

 

[PWD]

Hi combo, Mr Thicky here but I don't know the number of rows so I don't see how that can help. The COUNTA gives me the number I want, but I don't know how to incorporate that function into my formula.

Many thanks,
D€$

 

[combo]

Instead of 'AE Reattendances'!$N$2:N409) OFFSET with arguments:
- arg. 1: 'AE Reattendances'!$N$2,
- arg. 2 and 3: 0 (you want to begin range as in arg. 1),
- arg. 4: COUNTA('AE Reattendances'!A:A)-1 (just heignt of the range),
- arg. 5: 1 (one column in range).

combo

 

[PWD]

Wow, that's totally magnificent, thanx.

=SUMPRODUCT((1/COUNTIF(OFFSET('AE Reattendances'!$N$2,0,0,COUNTA('AE Reattendances'!$A:$A)-1,1),OFFSET('AE Reattendances'!$N$2,0,0,COUNTA('AE Reattendances'!$A:$A)-1,1)))*(OFFSET('AE Reattendances'!$F$2,0,0,COUNTA('AE Reattendances'!$A:$A)-1,1)='Weekly Activity Summary'!K$3))

Many thanks,
D€$