• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Named Range Reference through cell content

S

Sadhana

New Member
I have an effort tracking sheet which has column for each day. I have defined range name for each column. I need to show how amy cells has 'NA' in it for a specific range name. it should take todays date range automatically and show NA count for that perticular range. Is it possible?
 
Sadhanna


When you say NA I assume you mean the #N/A error

You can use the ISNA(Cell) function


Code: 
=If(ISNA(A1),Value if true, value if false)
 
I mean to say cell containing text 'NA'.


I want a fomula to search for Range which has today's date in it' name and count 'NA' containing cells in that perticular column.
 
Sadhanna,


I am not very sure about your requirement.


If you want to count cells that has NA text in a specified range, then you can use COUNTIF function as follows:


Say the range is A1:A10, then your formula would be:


=COUNTIF(A1:A10,"NA")


If this is not what you are looking for, then please copy paste some sample data here or consider uploading a sample workbook here:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik
 
Thank you Kaushik,


I have posted my sheet on dropbox. would you be able to check it.


The range is already defined in the 1st sheet... I wnat to know what to know people who are 'NA' for today whenever I opent the sheet. for example if today is 30Nov and opne the sheet I need to know count of NA in today's column.


I hope it will clarify your doubts.


Regards,

Sadhana
 
Hi Sadhana ,


Try this :


=IFERROR(COUNTIF(OFFSET(Data_Range,0,MATCH(TODAY(),Dates_Row,0)-1,ROWS(Data_Range),1),"NA"),0)


where Data_Range is a defined named range referring to =!$D$2:$AH$33


Dates_Row is a defined named range referring to =!$D$1:$AH$1


In case you do not have Excel 2007 or later , you cannot use the IFERROR function ; instead you will have to use the following formula :


=IF(ISERROR(COUNTIF(OFFSET(Data_Range,0,MATCH(TODAY(),Dates_Row,0)-1,ROWS(Data_Range),1),"NA")),0,COUNTIF(OFFSET(Data_Range,0,MATCH(TODAY(),Dates_Row,0)-1,ROWS(Data_Range),1),"NA"))


Narayan
 
Hello,


I have following table. now I need to count "NA" in the column where the date matches with current date. In the following case the answer will be 3.


1-Dec 2-Dec 3-Dec 4-Dec 5-Dec

NA NA NA NA NA

pid pid pid pid pid

pid pid pid pid pid

NA NA NA NA NA

NA NA NA NA NA


Please help
 
You must log in or register to reply here.
Back
Top