+ Reply to Thread
Results 1 to 5 of 5

ProductIF with Mutiple Criteria

  1. #1
    Registered User
    Join Date
    07-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    3

    ProductIF with Mutiple Criteria

    I need the formula to check the 2 "text" values and 1 numerical value in three different cells and if all three are valid, multiply their corresponding numerical values which are located in three other different cells. Here is my formula (which is not working):

    =PRODUCT(IF(b5 ="Text",d5="Text",e5=number,(s7*s33*s23),""))

    Any ideas?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: ProductIF with Mutiple Criteria

    Try perhaps..

    =IF(AND(ISTEXT(B5),ISTEXT(D5),ISNUMBER(E5)),S7*S33*S23,"")
    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.

  3. #3
    Registered User
    Join Date
    07-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: ProductIF with Mutiple Criteria

    That worked! Now the tricky part...The 2 "text" fields and 1 numerical field are actually pick lists that depending on what value is selected from the pick list, the values corresponding in the cells that need to be multiplied need to change.

    So for example, the following formula works for any text or any number that is entered in cells B5, D5, or E5:

    =IF(AND(ISTEXT(B5),ISTEXT(D5),ISNUMBER(E5)),S7*S33*S23,"")

    I need the formula to work as follows:

    =IF(AND(IS"Pick list1 TEXT value1"(B5),IS"Pick list2 TEXTvalue1"(D5),IS"Pick list3 NUMERICALRvalue1"(E5)),S7*S33*S23,"")

    Once I get that to work, I'll duplicate it for every pick list value:

    =IF(AND(IS"Pick list1 TEXT value2"(B5),IS"Pick list2 TEXTvalue2"(D5),IS"Pick list3 NUMBERvalue2"(E5)),S8*S34*S24,"")...etc, etc, etc

    Can that be done?

  4. #4
    Registered User
    Join Date
    07-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: ProductIF with Mutiple Criteria

    This is the actual formula that I tried first but I'm getting the #VAULE! error with it:

    =PRODUCT(IF(B5="January",D5="Stage 5",E5="50"),(S7*S33*S23),"")

    Again if it works, I'll keep duplicating. My next formula would be something like:

    =PRODUCT(IF(B5="February",D5="Stage 6",E5="100"),(S8*S34*S24),"")

    Anyway, can the

    =IF(AND(ISTEXT(B5),ISTEXT(D5),ISNUMBER(E5)),S7*S33*S23,"")

    formula be edited somehow to do what I'm looking for?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: ProductIF with Mutiple Criteria

    Do you mean?

    =IF(AND(B5="February",D5="Stage 6",E5=100),S8*S34*S24,"")

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1