+ Reply to Thread
Results 1 to 6 of 6

Product IF Function

  1. #1
    Registered User
    Join Date
    12-25-2014
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    3

    Product IF Function

    Hi Everyone,

    I'm trying to multiply values in column B if the values in column A meet a certain criteria. Example:
    Column A: discount type
    Column B: discount value

    Column A Column B
    Rebate 2%
    Rebate 1.5%
    Discount 3%
    Discount 2%
    Rebate 5%

    Ideally, it should multiply 2%*1.5%*5%.

    What's the easiest way to do this? I've only been seeing sumproducts but it doesn't really solve for my purpose.

    Thank you very much!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Product IF Function

    Hi, welcome to the forum

    This is probably way too simplitsic, but...
    =B2*B3*B6

    Otherwise, can you wxplain how you ID which values to use?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-25-2014
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    3

    Re: Product IF Function

    Hi Ford,

    Thanks for the quick reply! Unfortunately, that formula is indeed too simplistic...
    Column A would be the identifier. The type of discount (Column A) could be varied depending on customer contractual agreements.

    I guess what's not explained here is that there is a Column C which details the specifics of Column A (e.g. prompt payment discount, volume discount...etc.). The type of discount could then vary depending on customer contract.

    Hope this explains myself better?

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Product IF Function

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    Rebate
    2%
    ------
    Rebate
    0.00150%
    2
    Rebate
    1.50%
    3
    Discount
    3%
    4
    Discount
    2%
    5
    Rebate
    5%


    This array formula** entered in E1:

    =PRODUCT(IF(A1:A5=D1,B1:B5))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Format as Percentage to as many decimal places as you want.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    12-25-2014
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    3

    Re: Product IF Function

    Dear Tony, amazing! Thank you so much! It works

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Product IF Function

    You're welcome. Thanks for the feedback!

+ 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] Need Function that will find ordered product, and display the product code in a 2nd workbk
    By rollerden in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2012, 08:52 AM
  2. product function
    By spiderman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. product function
    By pilotjs in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  4. product function
    By pilotjs in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] product function
    By spiderman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2005, 09:06 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