Cell Referencing Issue

[jackson4u]

Hi All ,

I am a much newer into Excel. I am in charge of a School. The schools excel sheet is complicated. I have shorened so as to make you people 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.

This formula that is been currently used =COUNTIF(C1:C8,"=Abil") for getting the number of attendant hours per day. C1:C10 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.

 

[Jon von der Heyden]

Hi and welcome to the ExcelGuru forums.

The function one would use to call the cells contents and have it interpreted as a range is called INDIRECT, but I don't think you should use it. INDIRECT is an expensive function to use because it is volatile. And in your example I don't believe it is necessary.

Instead rather look at COUNTIFS, which allows you to perform a multi-conditional COUNT. I have attached a sample where a user inputs a date and COUNTIFS is used to yield a count of al occurences for that date, and for any individual chosen.

 

[jackson4u]

Thanks Jon for your help. That was really helpful..

But still the issue is not fixed as I have to use some more calculation in the excel sheet..

Here I need to calculate the daily Subject total using a COUNTIF function also, but it does not help in your example..
Is there a way to refer the Cell ?

You can download the excel sheet from here

If anyone know the solution, I do appreciate the reply...

 

[jackson4u]

Issue Solved!!!

I solved the issue.. Indirect() function in excel solved my issue..

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

I got help from MrExcel website. See the comment

http://www.mrexcel.com/forum/showthread.php?p=2739216&posted=1#post2739216

:clap2: Thanks MR EXCEL :clap2:

 

[jackson4u]

Hi and welcome to the ExcelGuru forums.

The function one would use to call the cells contents and have it interpreted as a range is called INDIRECT, but I don't think you should use it. INDIRECT is an expensive function to use because it is volatile. And in your example I don't believe it is necessary.

Instead rather look at COUNTIFS, which allows you to perform a multi-conditional COUNT. I have attached a sample where a user inputs a date and COUNTIFS is used to yield a count of al occurences for that date, and for any individual chosen.

Thanks man for Help !!

 

[Simon Lloyd]

Thanks for posting back your solution and the link

 

[Roger Govier]

Hi Jackson

As Jon said earlier, In direct is a very expensive function in terms of processing, especially as you say you have thousands of students x many days of data.

You do not need to use Indirect at all, just Countifs. Equally you do not need to give the range of cells containing your data, just a single entry of a date will do.

Take a look at the attached sheet.
I have inserted a header row with titles, so all references are moved down by one row.
In cell H4
=COUNTIFS($B:$B,$H$2,$C:$C,$G4,$D:$D,H$3)
and copied across to cell I4 and down through H5:H6

In J4 the formula Jon gave you
=COUNTIFS($C:$C,$G4,$B:$B,$H$2)

If you try it with your workbook, you will get 0 in the Science column, but that is because you have an extra space on the end of the word Science in cell H3

Better than using formulae though, is to use a Pivot Table, as I have set up for you.
I created a Table by placing my cursor within the data, having inserted a header row and chose Insert tab >Table>my table has headers.
This creates a dynamic table which grows in dimensions as you add more data.

Then with cursor inside table >Design tab>Summarize with Pivot Table>OK
On the new tab that is created,
drag Date to Report Filter area
drag Pupil to the Row label area
drag Subject to the Column label area
then drag Subject again to the Values area where it will become count of Subject.

Now, just use the Filter button on Date to select the date required and you get your report.