Results 1 to 11 of 11

Nested COUNTIF / IF formula for data validation workaround

Threaded View

HeyInKy Nested COUNTIF / IF formula... 03-03-2017, 12:58 PM
HeyInKy Re: Nested COUNTIF / IF... 03-03-2017, 01:38 PM
FDibbins Re: Nested COUNTIF / IF... 03-03-2017, 01:56 PM
HeyInKy Re: Nested COUNTIF / IF... 03-03-2017, 02:01 PM
FDibbins Re: Nested COUNTIF / IF... 03-03-2017, 02:05 PM
HeyInKy Re: Nested COUNTIF / IF... 03-03-2017, 02:06 PM
FDibbins Re: Nested COUNTIF / IF... 03-03-2017, 02:02 PM
HeyInKy Re: Nested COUNTIF / IF... 03-03-2017, 02:05 PM
FDibbins Re: Nested COUNTIF / IF... 03-03-2017, 02:23 PM
HeyInKy Re: Nested COUNTIF / IF... 03-03-2017, 02:27 PM
FDibbins Re: Nested COUNTIF / IF... 03-03-2017, 02:33 PM
  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Nested COUNTIF / IF formula for data validation workaround

    I have a table with several columns for end user entry. Because I'm using formulas in the cells, I can't use data validation (nor VBA), but I still want to let end users know if they've entered data correctly or not, so I created an additional helper column, col. W, to check their entries.

    There are 10 questions in Part 1, columns I-R (Q1-Q10), and an addition 4 questions in Part 2, columns S-V (Q11-Q14). The trick is the in column G asks if they are answering only the first 10 (Part 1), and all 10 must be either 'y' or 'n', or if they are answering all 14 questions, the last four answered with either, 'y', 'n', or 'n/a'.

    FYI, I have an additional column H (All Yes) which will automatically put y's in questions 1-10 or 1-14, depending on the entry in col. G. So I'm looking at a rather complex, nested IF and COUNTIF formula for column W, that will return either "OK" or "Error." It should return 'OK' if all 14 questions are being answered (no blanks) AND if Q1-Q10 are all answered either y or n AND if Q11-Q14 are answered y, n, or n/a. If there is a blank OR if there is an value other than y or n for Q1-Q10 or a value other than y, n, or n/a for Q11-Q14, then it should return "Error."

    In my sample, rows 2-4 should be "OK" in column W, and "Error" should be returned in rows 6 & 7.

    sample 1a.xlsx
    Last edited by HeyInKy; 03-03-2017 at 01:11 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nested COUNTIF and IF Formula
    By neurion in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2015, 05:34 AM
  2. Replies: 5
    Last Post: 07-28-2014, 11:53 AM
  3. [SOLVED] Workaround for Data Validation (List) limitation
    By abousetta in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-04-2013, 06:04 PM
  4. [SOLVED] Help with Data Vailidation workaround when cell has a formula
    By NANDO-T in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 11:31 PM
  5. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM
  6. workaround for exceeding 7 nested functions in Excel?
    By marcia21 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-07-2008, 09:27 AM
  7. Replies: 11
    Last Post: 04-21-2006, 11:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1