Countifs and indirect dynamic ranges

[BigAlzBub]

Hi,

I'm having difficulty with an excel problem using countifs and dynamic ranges
here a brief example of what I'm trying to do (the real thing is way more complicated)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Year
[/TD]
[TD]English
[/TD]
[TD]Maths
[/TD]
[/TR]
[TR]
[TD]Student1
[/TD]
[TD]9
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]Student2
[/TD]
[TD]9
[/TD]
[TD]d
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]Student3
[/TD]
[TD]10
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]Student4
[/TD]
[TD]11
[/TD]
[TD]c
[/TD]
[TD]c
[/TD]
[/TR]
</tbody>[/TABLE]

I've created dynamic ranges for all the columns (except name), with the following statement in the name manager.
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
obviously changing the first reference to account for the different columns
when I use the following statement everything works fine
=countifs(year,9,english,"c")
however if I type the word english in another cell (Eg E1) and then use an indirect statement I get a #ref error
=countifs(year,9,indirect(E1),"c")

Can anyone shed any light on this for me?
Thanks in advance.

 

[XOR LX]

Hi,

As I understand it, Named Ranges which are dynamic cannot be referenced using INDIRECT.

You need to use EVALUATE as a workaround. For example, with english in E1 as you give, create a new Named Range, english2 say:

=EVALUATE(Sheet1!$E$1)

Now use this in your formula instead (though with E1 still containing english, not english2):

=COUNTIFS(year,9,english2,"c")

Regards

 

[XOR LX]

Edit: english2 was not a very good choice of name! Especially since you will be using this cell as a means to reference whichever Named Range cell E1 contains. Perhaps something like Subject is a better choice.

Then =COUNTIFS(year,9,Subject,"c") will do as you require, i.e. perform the count based on whatever Named Range cell E1 contains, providing you follow my instructions as in my previous post.

Regards

 

[BigAlzBub]

Thanks a lot :D

 

[XOR LX]

You're welcome.