+ Reply to Thread
Results 1 to 7 of 7

Use boolean operator by referring to the cell address

  1. #1
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Use boolean operator by referring to the cell address

    My "Charges" sheet has following labels:
    A B C D
    Item-Names Date Month(of Date) Amount

    On "Summary" sheet, I use the formula below to sum all values for each item-name for certain periods only, by using boolean operators ex, <=6 (Jan to June), <5 (Jan to Apr), >3 (Apr to Current)

    Please Login or Register  to view this content.
    To make the formula more "dynamic" I placed a boolean operator (<=6) in cell address D2 and changed the formula using the INDIRECT function.

    Please Login or Register  to view this content.
    Needless to say, it did not work, keep getting #REF! errors no matter what I tried.

    Thank you for taking the time to read my post, any suggestion will be much appreciated.

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

    Re: Use boolean operator by referring to the cell address

    INDIRECT returns a range given a range expression. "<=6" is not a range expression. You cannot put a relational operator in an INDIRECT to make it dynamic (<= is a relational operator; the boolean operators are AND, OR, and NOT) .

    Are you saying you want this formula to sometimes use <=6 and sometimes maybe >6 ? You will have to replicate the formula with an IF function to do it each way. If you give more information about your overall problem we may be able to recommend a different approach that gets you what you need.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Re: Use boolean operator by referring to the cell address

    Hi Jeff,

    Sorry for the delay, but was very busy with work. Yes that is exactly what I want in D2. So you are saying that I must separate the boolean "<=" from the "number"? How would I go about doing that?

    What I'm trying to do is sum values in a range, if they fall within a month range that I want to decide in cell D2. Any suggestions would help. Again thank you for your help.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Use boolean operator by referring to the cell address

    Why not use SUMIFS, like this:

    =SUMIFS(Charges!$D$2:$D$13,Charges!$A$2:$A$13,$B3,Charges!$C$2:$C$13,"<="&$D$2)

    where D2 contains 6.

    Hope this helps.

    Pete

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

    Re: Use boolean operator by referring to the cell address

    Quote Originally Posted by Pete_UK View Post
    Why not use SUMIFS, like this:

    =SUMIFS(Charges!$D$2:$D$13,Charges!$A$2:$A$13,$B3,Charges!$C$2:$C$13,"<="&$D$2)

    where D2 contains 6.
    Good solution. I was so focused on what doesn't work that I didn't think about what does work. You can adapt this solution to what you described like this:

    =SUMIFS(Charges!$D$2:$D$13,Charges!$A$2:$A$13,$B3,Charges!$C$2:$C$13,$D$2)

    where $D$2 can be the strings

    <=6

    or

    >6

  6. #6
    Registered User
    Join Date
    12-11-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel Pro Plus 2019
    Posts
    67

    Thumbs up SOLVED: Use boolean operator by referring to the cell address

    Thank you both for your help.

    Like you wrote, "I was so focused on what doesn't work that I didn't think about what does work."

    Again thank you both.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: SOLVED: Use boolean operator by referring to the cell address

    Glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] entering a Boolean value into a cell
    By Trebor777 in forum Excel General
    Replies: 9
    Last Post: 05-27-2017, 12:08 PM
  2. [SOLVED] Exit Workbook_BeforeClose(Cancel As Boolean) if a public boolean = true
    By meabrams in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2016, 06:10 AM
  3. converting a string having a comparison operator into boolean
    By allsourav in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-07-2016, 11:37 AM
  4. Boolean operator within excel function
    By ncd99 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-11-2015, 02:04 PM
  5. Replies: 1
    Last Post: 08-23-2013, 05:45 PM
  6. Change cell value if Boolean is true
    By English_Bloke82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2013, 07:18 AM
  7. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) problem
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2009, 07:28 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