COUNTIF + ADDRESS Issue

[joshuadennin]

I am trying to count the number of cells in a specific column, and within arange in that colum, that are not zero. The column is located by the matchfunction, which is referencing another cell on the sheet. When I use the formula I have come up with within the sheet, it works like a charm. When I try to reference a column range in a different sheet, it does not return anaccurate value (normally 0 or 1).

Works within the sheet:
=COUNTA(INDIRECT(ADDRESS(2,MATCH(H13,1:1,0))&":"&ADDRESS(7,MATCH(H13,1:1,0))))

Where H13=name

Does not work with cell reference between sheets:
=COUNTA(INDIRECT(ADDRESS(2,MATCH(E4,'Prod by EE'!$C$1:$AC$1,0))&":"&ADDRESS((1+(C4*3)),MATCH(E4,'Prod by EE'!$C$1:$AC$1,0))))

Where E4=name, C4=number of weeks

 

[joeu2004]

When I try to reference a column range in a different sheet,
it does not return an accurate value (normally 0 or 1).
Works within the sheet:
=COUNTA(INDIRECT(ADDRESS(2,MATCH(H13,1:1,0))&":"&ADDRESS(7,MATCH(H13,1:1,0))))
[....]
Does not work with cell reference between sheets:
=COUNTA(INDIRECT(ADDRESS(2,MATCH(E4,'Prod by EE'!$C$1:$AC$1,0))&
":"&ADDRESS((1+(C4*3)),MATCH(E4,'Prod by EE'!$C$1:$AC$1,0))))
Where E4=name, C4=number of weeks

It would be helpful if you showed an example of what you intend for the
resulting COUNTA() form.

I assume something like =COUNTA('Prod by EE'!B2:B31) if MATCH() returns 2
and C4 is 30.

Look at the ADDRESS() usage on the help page. The worksheet name must be
specified in the 5th parameter.

So the following should provide the COUNTA() form that I assume:

=COUNTA(INDIRECT(ADDRESS(2,MATCH(E4,'Prod by EE'!$C$1:$AC$1,0),,,"Prod by
EE")
&":"&ADDRESS(1+C4*3,MATCH(E4,'Prod by EE'!$C$1:$AC$1,0))))

Note that the worksheet name is specified in only the first ADDRESS() call.
Again, look at the intended COUNTA() form above to see why.