+ Reply to Thread
Results 1 to 4 of 4

If and Statements - order of true or false values

  1. #1
    Registered User
    Join Date
    04-30-2014
    Location
    Hertford, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question If and Statements - order of true or false values

    I'm using if/and statements.

    With the attached spreadsheet, if Cell E6 AND column D6 meet both criteria I set, I want the return to be the result of a calculation, and left blank if neither of the criteria are met.

    This works fine for row 1 in my sheet; The year is 2019 (I have asked it to look for 2021), and although there is something in column D, a blank is returned. This is what I'm after.

    However for row 2, applying the same if/and formula, when both criteria are met - i.e. the year is 2021 and there is also something in column D, it still returns a blank.

    In row 3, I have switched the "value if True" and "value if False" and the calculation works.

    My simple understanding of IF/AND is it both criteria are met I want it to do this (value if true), but if they're not both met (value if false), then I want it to do that. Have I got this wrong? Why can't I get the if/and to work?

    N.B My attachment is a very simplified version of my actual working sheet. To simplify the calculation require imports minus exports = balance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: If and Statements - order of true or false values

    Welcome to the forum.

    Yes, if you put an AND function as the check in an IF function and both criteria in that AND function are met, then the 'value if True' (or 'calculation if True') will be returned. But you don't have any blanks in column D, so none of your AND functions are fully met.

    I suggest just adding an extra criterion to each of your SUMIFS to include the year and skipping the IF entirely. Is there a specific reason why you need to check for blanks?
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    04-30-2014
    Location
    Hertford, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: If and Statements - order of true or false values

    Thank you.

    This exercise started because I particularly want the cells to stay blank instead of returning a zero if one of the elements is not available. After a number of searches, I came across IF(AND).

    The weird thing was that even when I did remove one of the elements of data to test the sample, the function still didn't seem to work.

    However, your answer made me look at my data again and I've decided that I can remove the lines with 2019 data anyway.

    And now by telling the IF(AND) to look for "" rather than a particular value in the cells it works.

    Thank you once again.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: If and Statements - order of true or false values

    You're welcome, glad I was able to help you find a solution which works for you. Also thanks for marking the thread as Solved.

+ 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] Fetching true / false based on product in same order ID
    By Jonneh2k in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2021, 03:04 AM
  2. true false statements - need help!
    By Allerious in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2017, 06:18 AM
  3. Date/Time with True and False statements attached.
    By Claybri in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-17-2016, 11:49 AM
  4. Average of ranges marked by TRUE and FALSE statements
    By B_P in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2014, 03:34 PM
  5. [SOLVED] Using IF statements to get Value True or False
    By haleakala17 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2014, 03:03 PM
  6. If statements: Non-numerical characters returning false when true
    By JONNY981 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-24-2009, 10:28 AM
  7. True/false statements checking for range
    By krayziez in forum Excel General
    Replies: 5
    Last Post: 05-29-2006, 04:52 PM

Tags for this Thread

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