+ Reply to Thread
Results 1 to 6 of 6

Convert or add If Function to a formula

  1. 11-30-2011, 11:52 AM #1
    JohnDear
    JohnDear is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Macon, GA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Question Convert or add If Function to a formula

    Using Excel 2010
    Is there a way to convert or add an “If” function to the following formula? I use this formula to add the delivery date by vendor on my report (located on sheet2). However, if a date is not entered into column F for a certain vendor then 1/1/00 appears on the report (located on sheet2). I would like for the cell to be left blank if no date is entered in column F.
    Any help is appreciated.

    =SUMPRODUCT(('Sheet1'!$A$2:$A$5000=A8)*('Sheet1'!$B$2:$B$5000="Delivery"), 'Sheet1'!$F$2:$F$5000)
    Reply With QuoteRegister To Reply
  2. 11-30-2011, 12:12 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Convert or add If Function to a formula

    If you are using 2010, then SUMIFS would be more efficient...

    =SUMIFS('Sheet1'!$F$2:$F$5000,'Sheet1'!$A$2:$A$5000,A8,'Sheet1'!$B$2:$B$5000,"Delivery")

    Then you can say:

    =IF(SUMIFS('Sheet1'!$F$2:$F$5000,'Sheet1'!$A$2:$A$5000,A8,'Sheet1'!$B$2:$B$5000,"Delivery"),SUMIFS('Sheet1'!$F$2:$F$5000,'Sheet1'!$A$2:$A$5000,A8,'Sheet1'!$B$2:$B$5000,"Delivery"),"")

    or you can use custom formatting to hide 0's with first formula: for example: m/d/yyyy;;;@
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 11-30-2011, 12:50 PM #3
    JohnDear
    JohnDear is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Macon, GA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Convert or add If Function to a formula

    I received a #VALUE! error for the 1st suggestion

    Not sure how to custom format a date within a formula
    Last edited by JohnDear; 11-30-2011 at 12:56 PM.
    Reply With QuoteRegister To Reply
  4. 11-30-2011, 01:09 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Convert or add If Function to a formula

    If the Sumproduct formula you posted worked, then I think the first SUMIFS formula should work.

    Do you have #VALUE errors in the range anywhere?
    Reply With QuoteRegister To Reply
  5. 12-01-2011, 12:48 PM #5
    JohnDear
    JohnDear is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Macon, GA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Convert or add If Function to a formula

    Well on sheet 2 column A (A8) is text. Would that cause the problem.
    Reply With QuoteRegister To Reply
  6. 12-01-2011, 02:45 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Convert or add If Function to a formula

    No, it shouldn't.

    Can you post the workbook (removing/replacing confidential data)?
    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)

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