Formula for multiple "IF AND" combinations

    Hello,


    I am trying to deliver a value in column "T" based on the value of 2 other columns (C & H). There are 18 possible combinations for C&H (3 options for C and 6 for H). I tried an imbedded IF AND statement and it worked until I saved it and came back, then I had the #VALUE error.
    Is there another formula/method to solve for this? My basic data set it below:


    If C1= AND H1= Then T1=
     H S DataA H R DataB H B DataC H T DataD H I DataE H M DataF M S DataG M R DataH M B DataI M T DataJ M I DataK M S DataL
    M M DataM L R DataN L B DataO L T DataP L I DataQ L S DataR L M DataS

    Thank you!

    Re: Formula for multiple "IF AND" combinations


    Try something like:

    =VLOOKUP(C1&"_"&H1,{"A_A",”X”;"A_B",”Y”;"A_C",Z;....},2,0)

    Where the “A_A”, “A_B”, “A_C” are the C and H combinations allowed (separated with an underscored to avoid false positive combinations), and beside each is the expected result for T (i.e. “X”,”Y”,”Z”….)

    Or you can simply list the possible combinations (again separated with underscore) in one column (Say P1:P10) and the corresponding T results in say Q1:Q10, then use VLOOKUP

    =VLOOKUP(C1&”_”&H1,$P$1:$Q$10,2,0)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula for multiple "IF AND" combinations


    Thank you so much - I tried the latter and it worked perfectly. I need to get familiar with the VLOOKUP Function as I think it would solve a lot of my needs, but I am still unsure as to how to go about it!


    I have one more I cannot figure out that may benefit from a VLOOKUP but I'm unsure . . . In this case I need to add data together:


    Column B = Name
    Columns C, E, G, I = Month
    Columns D, F, H, J = # Hours


    On a separate worksheet I need to summarize the # of hours for anywhere Name X is listed AND Month Y is listed.


    Can you help with this?


    Thanks!
    Laura

    Re: Formula for multiple "IF AND" combinations


    Little unsure about what you are looking for from your description, any chance you can attach an example sheet to better understand the problem?

    Re: Formula for multiple "IF AND" combinations


    I think maybe you need Sumproduct()

    e.g.

    =SUMPRODUCT(($B$2:$B$100="John")*($C$2:$I$100="March"),$D$2:$J$100)

    Or, if you have actual dates in the Month columns…

    =SUMPRODUCT(($B$2:$B$100="John")*(TEXT($C$2:$I$100,”MMM”)="March"),$D$2:$J$100)

    You can replace the “John” and “March” with cell references containing that information

    Also, adjust ranges to suit.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula for multiple "IF AND" combinations


    One final questions (I hope)!


    Can SUMPRODUCT work if I only have one criteria? I now need to deliver the SUM based on just the Month. Data is as:


    Columns A, C, E, G, I, K = Month Year (Text)
    Columns B, D, F, H, J, L = # of Hours


    I need to add the hours together for anywhere a specific Month Year appears.

    Re: Formula for multiple "IF AND" combinations


    Do you mean like? =SUMPRODUCT(--($A$2:$K$100="March 2013"),$B$2:$L$100)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula for multiple "IF AND" combinations


    Yes! That appears to work. I was missing the two dashes (--) after the first parenthesis . . . for my education, why are those necessary?

    Re: Formula for multiple "IF AND" combinations


    They coerce the TRUE/FALSE results you get to 1/0 respectively so that the arithmetic could be done. On the previous formula, you had more than one condition. The multiplication of the conditions, causes the same effect (I.e. coerces the conversion of True/False), so then the -- are not needed there.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula for multiple "IF AND" combinations


    Thank you.


    I thought this was working, but this morning it isn't . . . I think it may because of the way the source data is formatted . . . Can you take a look for me?


    The formula I'm using is highlighted on tab ONE, and the source data is on tab TWO.


    Thanks for your continued help!

    Re: Formula for multiple "IF AND" combinations


    you have mixed date styles in your "two" sheet....


    Try:


    =SUMPRODUCT(--(DATE(YEAR(Two!$W$3:$AS$10),MONTH(Two!$W$3:$AS$10),1)=DATE(YEAR(A7),MONTH(A7),1)),Two!$X$3:$AT$10)


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login