+ Reply to Thread
Results 1 to 3 of 3

help to add back COUNTIF after amending formula

  1. #1
    Registered User
    Join Date
    06-28-2017
    Location
    singapore
    MS-Off Ver
    2010
    Posts
    13

    help to add back COUNTIF after amending formula

    Hi

    I had the below formula that would check for breaks on a rec table and if there is a combination of two and all cells above from cell Q2 to the one directly above are empty it will populate as "Extra"

    =IF(AND('Rec table'!J10=FALSE,COUNTIF(Q$2:Q9,"")=ROW(Q9)-1,'Rec table'!M10=FALSE),"Extra","")

    the above was working ok but I needed to add extra arguments to make the results more accurate but I am not sure how to add back the Countif to only populate the result when the cells above are empty

    =IF(AND(OR('Rec table'!J12=FALSE,'Rec table'!M12=FALSE))*OR('Rec table'!G12=FALSE,'Rec table'!M12=FALSE)*OR('Rec table'!G12=FALSE,'Rec table'!J12=FALSE),"Extra","")

    Can someone help with where I should put it back in to get the desired result?

    *Sorry I cannot add the spreadsheet as I am unable to upload anything on my work machine

    Thanks

  2. #2
    Registered User
    Join Date
    08-28-2015
    Location
    Melbourne, AUS
    MS-Off Ver
    2011
    Posts
    59

    Re: help to add back COUNTIF after amending formula

    It's hard to say exactly what you are after without any reference, is this the one?

    =IF(AND(OR('Rec table'!J12=FALSE,'Rec table'!M12=FALSE),COUNTIF(Q$2:Q9,"")=ROW(Q9)-1,OR('Rec table'!G12=FALSE,'Rec table'!M12=FALSE),OR('Rec table'!G12=FALSE,'Rec table'!J12=FALSE)),"Extra","")
    Last edited by mrshl9898; 08-03-2017 at 10:02 PM.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: help to add back COUNTIF after amending formula

    First, this subformula doesn't benefit by adding AND:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It is equivalent to the same thing without the AND

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also I see you are multiplying logical expressions together. There is nothing wrong with that; it is equivalent to AND, although I find it a little "tricky" and less obvious. Anyway, adding back your COUNTIF as a necessary condition produces this, keeping with using *

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here is the same thing just using logical functions:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    To break this down, it means

    IF
    At least one of these is FALSE: J12, M12
    AND
    At least one of these is FALSE: G12, M12
    AND
    At least one of these is FALSE: G12, J12
    AND
    COUNTIF(Q$2:Q9,"")=ROW(Q9)-1
    THEN
    Result is Extra
    ELSE
    Result is null string

    Your three OR statements ANDed together are logically equivalent to this

    AND('Rec table'!J12=FALSE,'Rec table'!M12=FALSE)

    (I will show you the truth table if you are not convinced)

    So your formula can be logically reduced:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 08-03-2017 at 10:10 PM. Reason: Added sheet qualifier in final formula
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] amending existing formula to blank when there is no data
    By chumster in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2017, 04:03 AM
  2. [SOLVED] amending existing formula to return blank when there is no data
    By chumster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2017, 03:30 AM
  3. Replies: 2
    Last Post: 12-03-2014, 08:19 AM
  4. SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?
    By domgilberto in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-22-2014, 06:18 PM
  5. [SOLVED] Amending a Key formula
    By pauldaddyadams in forum Excel General
    Replies: 18
    Last Post: 10-06-2012, 04:37 PM
  6. Amending formula to divide by the first number in the range used.
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2012, 09:38 AM
  7. Amending a large formula
    By isolation123 in forum Excel General
    Replies: 10
    Last Post: 03-26-2008, 12:49 PM

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