+ Reply to Thread
Results 1 to 7 of 7

IF Function cannot work out the formula

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    aberdeen
    MS-Off Ver
    Excel 365
    Posts
    20

    Unhappy IF Function cannot work out the formula

    I have a spreadsheet for the month of May.

    Every time there is a piece of equipment gone out for the month, we put 1.00 against the date.

    However, there is a minimum set that is sent out even though we have a 1.00 against the date to mark its gone out the qty might be 4.

    I need to work out the total amount we bill each day, instead of for each piece of equipment at the end of the month and any formula I try I keep getting errors.

    It is only to calculate if there is a 1 in the box on the date and it’s the total for that day for each piece of equipment.

    I hope I explained this ok
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: IF Function cannot work out the formula

    I suggest updating your sample workbook with the desired results.

    Once we see that, we can create a formula to produce them automatically.

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    aberdeen
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: IF Function cannot work out the formula

    Thanks please see attached
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: IF Function cannot work out the formula

    I understand how you get 49.44 for D11 but I don't see how you get 9 for C11.

    Can you explain that one?
    Last edited by 63falcondude; 06-05-2018 at 12:01 PM.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: IF Function cannot work out the formula

    If you are trying to sum the products of Qty and Price, only when there is a 1 in the column, try this in C11:

    =SUMPRODUCT((C2:C9=1)*$A2:$A9,$B2:$B9)

    Drag across through AG11.

    Or since you are using 1 or nothing (0) in the column, you can shorten it to this:

    =SUMPRODUCT(C2:C9,$A2:$A9,$B2:$B9)
    Last edited by 63falcondude; 06-05-2018 at 12:13 PM.

  6. #6
    Registered User
    Join Date
    07-16-2013
    Location
    aberdeen
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: IF Function cannot work out the formula

    Genuis thank you very much!

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: IF Function cannot work out the formula

    You're welcome. Happy to help.

+ 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. Replies: 3
    Last Post: 09-26-2016, 02:42 PM
  2. [SOLVED] formula in an IF function doesn't work, syntax?
    By napadude in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2015, 06:07 AM
  3. Indirect function causing formula not to work
    By JohnDoh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2015, 11:48 AM
  4. Trying to create UDF to produce different results depending on different inputs
    By forrestgump1980 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2014, 09:58 AM
  5. is there a formula with the IF function that would work?
    By jgrever2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-11-2011, 04:15 PM
  6. Replies: 3
    Last Post: 01-11-2011, 11:02 PM
  7. Unable to get Array Formula Using Sum Function to work
    By kingjams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2007, 07:19 AM

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