Results 1 to 7 of 7

Thread: Cell Referencing Issue

  1. #1

    Cell Referencing Issue



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Excel Issue.jpg 
Views:	4 
Size:	44.1 KB 
ID:	91  

  2. #2
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    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.
    Attached Files Attached Files
    Regards,
    Jon von der Heyden

  3. #3
    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...

  4. #4

    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/showthr...=1#post2739216

    Thanks MR EXCEL

  5. #5
    Quote Originally Posted by Jon von der Heyden View Post
    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 !!

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Thanks for posting back your solution and the link
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •