# 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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

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

• Re: Formula for multiple &quot;IF AND&quot; combinations

This site continues to save my life! Thank you - SUMPRODUCT worked!

• Re: Formula for multiple &quot;IF AND&quot; 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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 &quot;IF AND&quot; 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.

## Files

• Re: Formula for multiple &quot;IF AND&quot; 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!