+ Reply to Thread
Results 1 to 7 of 7

Want changes in "if" function

Hybrid View

  1. 11-15-2016, 09:17 AM #1
    rajeshn_in
    rajeshn_in is offline
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Want changes in "if" function

    Hi friends,

    I am using IF function as below

     =SUM(IF(A2:A6>=B9,IF(A2:A6<=B10,IF(B2:B6=B11,C2:C6))))
    in this formula, if B2:B6=B11, then I want C2:C6 total according to date & B2:B6 values .....,

    But B11 = "", then I want C2:C6 total according to date. .....

    Generally with above formula without B11, I am getting error....., other wise I have some big formula as below, can we reduce the formula size or any other formula......

     =SUM(IF(A2:A6>=B9,IF(A2:A6<=B10,IF(B2:B6=B11,C2:C6), IF(A2:A6>=B9,IF(A2:A6<=B10,C2:C6))))

    Thank you,
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-15-2016, 09:33 AM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Want changes in "if" function

    Try this
    Formula: copy to clipboard 
    =SUMIFS(C:C,A:A,">="&B9,A:A,"<="&B10,B:B,B11)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 11-15-2016, 09:44 AM #3
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    5,999

    Re: Want changes in "if" function

    Try this ...

    =SUM(IF(B11="",C2:C6,IF((A2:A6>=B9)*(A2:A6<=B10)*(B2:B6=B11),C2:C6)))

    Enter with Ctrl+Shift+Enter.
    Reply With QuoteRegister To Reply
  4. 11-15-2016, 09:50 AM #4
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    5,999

    Re: Want changes in "if" function

    Or ...

    =SUMIFS(C2:C6,A2:A6,">="&B9,A2:A6,"<="&B10,B2:B6,B11&"*")
    Reply With QuoteRegister To Reply
  5. 11-15-2016, 12:22 PM #5
    rajeshn_in
    rajeshn_in is offline
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Want changes in "if" function

    Thank you very much Phuocam
    Reply With QuoteRegister To Reply
  6. 11-15-2016, 09:50 AM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Want changes in "if" function

    I think I missed B11="" part
    Corrected
    Formula: copy to clipboard 
    =IF(B11="",SUM(C:C),SUMIFS(C:C,A:A,">="&B9,A:A,"<="&B10,B:B,B11))
    Last edited by AlKey; 11-15-2016 at 09:55 AM.
    Reply With QuoteRegister To Reply
  7. 11-15-2016, 12:21 PM #7
    rajeshn_in
    rajeshn_in is offline
    Forum Contributor
    Join Date
    04-06-2016
    Location
    Singapore
    MS-Off Ver
    MS Office 365 & 2010
    Posts
    698

    Re: Want changes in "if" function

    Thank you very much Alkey
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to use InStr function to search for a text that starts with "CTF" and ends with "."?
    By yoursamrit2000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2014, 06:52 PM
  2. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. A function to change "text" to "numbers" so the result can be calculated.
    By skip2 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2012, 10:23 AM
  5. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM
  6. Please add a "sheet" function like "row" and "column" functions
    By Spreadsheet Monkey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2005, 12:15 PM
  7. Replace "insert function" with "edit formula" button in fourmula b
    By 13brian in forum Excel General
    Replies: 0
    Last Post: 08-24-2005, 04:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1