Forum Discussion

jhcj's avatar
jhcj
Copper Contributor
Dec 05, 2024
Solved

Mutiplying values based on criteria

Hi all, appreciate some help. I'm trying to get excel to multiply values in column M with column G based on criterias in column E and F. 

 

For example,

If item type is 20DV and category is Y, then mutiply value in column M with 2. 

If item type is 40ST and category is Y, then multiply value in column M with 4

 

Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Dec 05, 2024

    so there are few ways and assuming you don't need any particular exceptions for having combinations that don't exist or have multiple options your could use FILTER.  I will use [ ] to indicate the related cells on that sheet and LU[ ] to indicate the LookUp table values so something like:

    =[# cont] * FILTER( LU[multiplier], (LU[type]=[Cont type])*(LU[category]=[ODG]), 0)

    but that said I like where Peter was going with using a formula to calculate the multiplier instead of a lookup.  Based on the pattern I see I might say:

    =LET(
    TypeFactor, IFERROR( LEFT( [Cont type], 1) / 2 , 0),
    CatFactor, SWITCH( [ODG], "Y", 2 , "N", 1 , "OHOW", 4, "OHOW2", 6 , 0),
    [# cont] * TypeFactor * CatFactor
    )

     

     

5 Replies

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Dec 06, 2024

    Please tr on below:

     

    =IF(AND(E2="20DV", F2="Y"), M2*2, IF(AND(E2="40ST", F2="Y"), M2*4, ""))

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 05, 2024

    The question is not sufficiently specific.  How is the item type and category you are interest in specified?

    At present a possible answer might be

    = LET(
    typeValue,  LEFT(type,2)/10,
    catValue,   SWITCH(category,"Y",1,"N",0.5,0),
    multiplier, typeValue * catValue,
    result,     count * multiplier,
    result
  )

    but I would be very surprised if that were the answer you require

    • jhcj's avatar
      jhcj
      Copper Contributor
      Dec 05, 2024

      Apologies for not being more specific. My spreadsheet looks like this: 

      Right now, depending on the values in column G and H, I would need to multiply values in column I accordingly to get the corresponding values in column J. 

       

      I would like to find a way to automate this, using the criterias that I provided in my original post (columns E-G). I would most likely put the criterias in these 3 columns in a separate sheet.

       

      Hope this further clarifies the assistance I require? 

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Dec 05, 2024

        so there are few ways and assuming you don't need any particular exceptions for having combinations that don't exist or have multiple options your could use FILTER.  I will use [ ] to indicate the related cells on that sheet and LU[ ] to indicate the LookUp table values so something like:

        =[# cont] * FILTER( LU[multiplier], (LU[type]=[Cont type])*(LU[category]=[ODG]), 0)

        but that said I like where Peter was going with using a formula to calculate the multiplier instead of a lookup.  Based on the pattern I see I might say:

        =LET(
    TypeFactor, IFERROR( LEFT( [Cont type], 1) / 2 , 0),
    CatFactor, SWITCH( [ODG], "Y", 2 , "N", 1 , "OHOW", 4, "OHOW2", 6 , 0),
    [# cont] * TypeFactor * CatFactor
    )

         

         

Resources