Cell Referencing inside Formula

[jackson4u]

I am a much newer into Excel. I am in charge of a School database. The schools excel sheet is complicated. I have shorened it for you so as to easy understand it.

Please see my Issue mentioned in the image below. Since there are thousands for Students and attendent hours for each day needs to be calculated. It has become very tedious as I have to find and replace the Cell references each day.

This formuala is been currently used =COUNTIF(C1:C8,"=Abil") for getting the number of attendent hours. c1:c18 denotes the day students count for one day.

My issue is that I need to refer a CELL (C13) instead of direct using the formula in Feild Instead of =COUNTIF(C1:C8,"=Abil"), I need something like =COUNTIF(Value(C13),"=Abil") to get the count of that particular day (say June 01).

I even tried with =COUNTIF(Cell("contents",C13),"=Abil") to get the cell reference, it did not worked.

I know that my issue is simple, I spend quite a long time for solving the issue, but was not able to do so.

Please do help

Thanks in advance.

 

[yogeshmaney]

Why not pivot

 

[Roli001]

try this:

=COUNTIF(INDIRECT(C13),"Abil")

 

[Meedan]

You can use Indirect formula.

=COUNTIF(INDIRECT(C13),"=Abil"),

 

[jackson4u]

try this:

=COUNTIF(INDIRECT(C13),"Abil")

That worked.. Indirect(Cell) solved my issue.. Pretty Cool.. Issue solved..
Thanks man

 

[jackson4u]

You can use Indirect formula.

=COUNTIF(INDIRECT(C13),"=Abil"),

Thanks Meedan for solving my Issue
It was really helpful

 

[Roli001]

That worked.. Indirect(Cell) solved my issue.. Pretty Cool.. Issue solved..
Thanks man

No problem. Thanks for the feedback.

 

[Ken Puls]

Hi folks,

Just a heads up that this one got cross posted and also has some replies at my forum. (http://www.excelguru.ca/forums/showthread.php?195-Cell-Referencing-Issue)

Roger Govier has posted a solution there which avoids the use of INDIRECT if anyone is interested.