Nested IF conditional on many cells...

[mrexcel27]

Hi all, trying to get a formula that executes the following if a number of conditions are met, but struggling a bit.

If cell range C21:28 all equal 'value x' then in cell C34 display 'Statement A'
If cell range C21:28 all equal 'value x' and cell range C29:31 all equal 'value y' then in cell C34 display 'Statement B'
If cell range C21:28 all equal 'value x' and cell range C29:31 all equal 'value y' and cell range C32:33 all equal 'value z' then in cell C34 display 'Statement C'
If none of the above apply, then in cell C34 display 'Statement D'

Thanks in advance.

[Tony Valko]

Is/are 'value x, y and z' numbers or text strings or both?

[mrexcel27]

Hi Tony and thanks for your reply.

They are numbers, but I'd like it not to matter if they are text strings, as I'd like to adapt this worksheet in the future for other purposes.

Regards

[Jonmo1]

Is it a situation where 'Value x' will be filled incrimentally from the top down?

i.e., C25 will never equal 'value x' if C21:C24 do not already equal 'value x' ??

[mrexcel27]

Is it a situation where 'Value x' will be filled incrimentally from the top down?

i.e., C25 will never equal 'value x' if C21:C24 do not already equal 'value x' ??

Hi, I wouldn't have thought it necessary to account for this, but they aren't necessarily filled incrementally. Only if each (all) of the cells in the range C21:C25 match the specified value, no matter in what order they are input then should display 'Statement A'. If one of the values is missing (up until they are all input), then it should display 'Statement D'

Regards

[Jonmo1]

It wasn't 'necessary' to account for that.
But it would have made the formula much easier if it was true..

Here's a brute force method, there may be easier more elegant way.

=IF(COUNTIF(C21:C33,"Value x")=13,"Statement C",IF(COUNTIF(C21:C31,"Value x")=11,"Statement B",IF(COUNTIF(C21:C28,"Value x")=8,"Statement A","Statement D")))

[FDibbins]

It might be easier to understand what you want if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[mrexcel27]

It wasn't 'necessary' to account for that.
But it would have made the formula much easier if it was true..

Here's a brute force method, there may be easier more elegant way.

=IF(COUNTIF(C21:C33,"Value x")=13,"Statement C",IF(COUNTIF(C21:C31,"Value x")=11,"Statement B",IF(COUNTIF(C21:C28,"Value x")=8,"Statement A","Statement D")))

Hi there - this works as a fashion, but only because I can substitute '>smallest value' (which happens to be 'value x') although the formula does not include a means of checking that the input values are the correct input values for 'value x' 'value y' and 'value z' as it would if the statement included the actual values, otherwise would display 'Statement D' as a pointer that everything was not as it should be...

It might be easier to understand what you want if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Not easy to do as the spreadsheet has many pages each with confidential customer info that I can't really upload. But can basically paste into an otherwise empty excel sheet with cells C21:28 'value x' which is 70, 'value y' which is 80, into cells C29:31, and 'value z' which is 90, into cells C32:33 and run the formula to produce the text string in C34.

Regards

[Jonmo1]

Yep, I completely missed the x y and z part. Thought it was all x..

Try
=IFERROR(INDEX({"C";"B";"A";"A"},MATCH(--(--(COUNTIF(C21:C28,"x")=8)&--(COUNTIF(C29:C31,"y")=3)&--(COUNTIF(C32:C33,"z")=2)),{111;110;100;101},0)),"D")

Not easy to do as the spreadsheet has many pages each with confidential customer info

We don't need to actually see ALL the pages...
Just the relevant sheet, and make up phony (but similar) information.

[Tony Valko]

They are numbers, but I'd like it not to matter if they are text strings, as I'd like to adapt this worksheet in the future for other purposes.

If the variables are TEXT strings then they must be double quoted within the formula. If they are numbers they do not need to be quoted.

This array formula**:

=IF(AND(C21:C28="value x",C29:C31="value y",C32:C33="value z"),"Statement C",IF(AND(C21:C28="value x",C29:C31="value y"),"Statement B",IF(AND(C21:C28="value x"),"Statement A","Statement D")))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[mrexcel27]

Hi Tony, many thanks for this...

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.