+ Reply to Thread
Results 1 to 9 of 9

IF/or with multiplication

  1. #1
    Registered User
    Join Date
    01-09-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    23

    IF/or with multiplication

    Hi there,

    I'm looking to create an IF/OR statement. Basically, im looking to have a formula where if the cell in "C" says Bill or John, multiply the cell in "F" by 2. So far I only have the 1 if statement.

    =PRODUCT(IFS($C:$C="bill",$F:$F*2))

    Also, how would I do this if I wanted to multiply by different numbers. Eg. Bill to muliply by 2 and John to multiply 4

    Also, how would I add a 3rd name? Any help would be great!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: IF/or with multiplication

    A simple way to do this, especially since the list of names to be matched and the multipliers is dynamic, would be to create a table and then sum the values of that table. The table itself could be moved, say to the far right hand columns of the worksheet (XFB:XFD) as in the attached file, and/or hidden so that all the end user sees is the result. The formula that yields the result is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula that populates the product column is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The Name Mtch column is the list of names that you want to find and the Multiplier column is the list of multipliers.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    01-09-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    23

    Re: IF/or with multiplication

    thank you, very helpful response

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: IF/or with multiplication

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  5. #5
    Registered User
    Join Date
    01-09-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    23

    Re: IF/or with multiplication

    Hi again,

    After further review, I'm having some difficulty with the formula provided as it doesn't quite work with what I'm trying to do.

    I've attached a short workbook of what I would need if I decide to do the table with multiplies. I'm sure this is quite easy, but I'm having difficulty. Any help would be great
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: IF/or with multiplication

    Assuming that the multiplier for those names that are not listed is one here is a formula that can be pasted into C2 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    To change the range E1:F3 into a table:
    1) Select one of the cells in the range,
    2) Simultaneously press Ctrl and T,
    3) Make sure that the correct range is listed and 'My table has headers' is checked in the pop up window,
    4) Press OK.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    01-09-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    23

    Re: IF/or with multiplication

    Hi again,

    It works great for the names listed in the table; now for example, a name that I do not have listed in a table (because I want the number to be equal to the value...as seen in the worksheet), should I just add the name to the table and have the multipier as 1?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,100

    Re: IF/or with multiplication

    Should not need to do anything. Notice that Joey isn't listed on the table and his total value = his number.
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-09-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    23

    Re: IF/or with multiplication

    that'll work! Thank you. I'm still configuring this worksheet out so I'll keep this thread alive in order to reference back to it if I have any future issues (i'm sure I will). When I have the kinks ironed out, I'll notify this one as solved!

+ 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. Multiplication and Subtotal
    By primobolan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-16-2013, 11:46 PM
  2. Multiplication
    By kmarie630 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2009, 03:59 PM
  3. Help with multiplication
    By GatorRaucous in forum Excel General
    Replies: 3
    Last Post: 02-07-2009, 04:44 PM
  4. multiplication
    By cevahir in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2007, 06:37 PM
  5. Multiplication formula
    By yuvosh in forum Excel General
    Replies: 5
    Last Post: 03-05-2007, 08:05 AM
  6. [SOLVED] multiplication
    By gem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2006, 03:55 AM
  7. avoiding multiplication
    By weeclaire in forum Excel General
    Replies: 4
    Last Post: 04-07-2006, 09:07 AM
  8. [SOLVED] Transpose multiplication
    By Cap Kirk in forum Excel General
    Replies: 3
    Last Post: 10-21-2005, 08:05 AM

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