Hi Aamir, I think you mean:
=FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$L$1)),IF(NOT(ISNA($A$1:$L$1)),COL UMN($A$1:$L$1))) If A1 contains a number instead of the #N/A error, the above formula will return 0. If you put the above formula in a vertical range of cells (instead of just one cell) and use the Evaluate Formula feature, you can see how Excel evaluates the formula step-by-step. The above formula counts how many NA errors there are in each sequence. In-between each sequence of NA errors, it counts some zeros. I used MATCH to find the position of the first sequence > 0 and INDEX to return the frequency/count for that sequence using the MATCHed position. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Aamir Shahzad Sent: Saturday, April 21, 2012 6:14 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Count intial #NA in a row using excel function Dear Asa, Only below formula is getting the desired result, but why you further enhanced your formula, I am not understanding, can you brief. =FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$L$1)),IF(NOT(ISNA($A$1:$L$1)) Regards, Aamir Shahzad On Sat, Apr 21, 2012 at 4:30 AM, Asa Rossoff <a...@lovetour.info> wrote: Hello Divaker! You provided very little information about your need. My reading of the subject line of your message ("Count initial #NA in a row.") leads me to believe you would like a formula that: 1. Finds the first appearance of #NA in the sequence of cells. 2. Counts the number of #NAs from that point that are in one uninterrupted sequence. 3. Ignores all other #NAs in the range. Here is a formula for the above: =INDEX(FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$L$1)),IF(NOT(ISNA($A$1:$L$1 )),COLUMN($A$1:$L$1))),MATCH(TRUE,FREQUENCY(IF(ISNA($A$1:$L$1),COLUMN($A$1:$ L$1)),IF(NOT(ISNA($A$1:$L$1)),COLUMN($A$1:$L$1)))>0,0)) Enter with CSE (ctrl-shift-enter) ----- array formula. Something tells me it could be done with a shorter formula, but this is the best I've got for you at the moment :) See Attached sample, or the same via Google Docs: https://docs.google.com/open?id=1nXP7jG6Obg1IHKhSr28outPlddX6zMKW3lWSd-I9_XA (@ GDocs, File>Download or File>Export to Google Spreadsheet) Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Friday, April 20, 2012 4:36 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Count intial #NA in a row using excel function Hi Expert, Please see attached sheet. Divaker -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- Regards, Aamir Shahzad -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
