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)
<tbody>
</tbody>
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.
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)
Name | Year | English | Maths |
Student1 | 9 | c | d |
Student2 | 9 | d | b |
Student3 | 10 | b | c |
Student4 | 11 | c | c |
<tbody>
</tbody>
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.