If statement with multiple ORs

[playpbateman]

Hello,

So I am performing a sum if the value from a cell is equal to any one of the multiple conditions I am providing in separate cells. The formula I currently have works, however it is very bulky because I have three conditions I want to compare to. This is what I have:


=IF(OR($'Complete Day Chart'.$A$52="All";$'Complete Day Chart'.$A$52=

O$2;$'Complete Day Chart'.$A$52=O$4;$'Complete Day Chart'.$A$52=O$1);...)

I was wondering if there was a way to only have to write the cell I want to compare once followed by its conditions. Something along the lines of:​

=IF($'Complete Day Chart'.$A$52=OR("All";O$1;O$2;O$4);...)​

Thanks for the help​

 

[schielrn]

This is one way:

=IF(ISNUMBER(MATCH($'Complete Day Chart'.$A$52;{"All";O$1;O$2;O$4};0));...)

or:

=IF(OR($'Complete Day Chart'.$A$52={"All";O$1;O$2;O$4});...)

Not sure if my semi-colons or commas are correct for a non-US version of Excel.

Hope that helps.

Edit: Nevermind I forgot you cannot put cell references (only hard-coded values like "All") into the brackets so the 2 approaches above is no good, I will post back with something else.

 

[playpbateman]

Thank you for the reply! Theoretically I feel like that should have worked, I don't know why I didn't think about using an array, however, for some reason it did not. The reason for the semicolons are that I'm actually using open office, but am familiar enough with both programs and their similarities that I should be able to change the slight differences.

The error its returning to me is an Err:512, formula overflow. This could be caused by the mishandling of the array, but I'm not really sure

 

[playpbateman]

Hi schielrn,

So I found out that the problem isn't the array in the MATCH() statement but the fact that I am referencing cells in the array. It worked when I substituted the references for the hand written values but that would even further enlongate more cell equations. I was wondering if you know if there is a way to get around that.

Thanks!******** type=text/javascript> vbmenu_register("postmenu_2392713", true); *********>

 

[Salamander]

Hi,

I'm not sure if it's less clunky or not but I'd use the following:

=OR(A52="All",A52=O1,A52=O2,A52=O4)*SUM(B2:B48)

If the OR = true it'll show the sum, otherwise it will show 0 which you could obviously amend to show as null or whatever by wrapping it in an IF...


S.

 

[Salamander]

Also on the idea of making less clunky you could name the cell to something more readable and just reference that in the formula:

=OR(A52="All",A52=Name1,A52=Name2,A52=Name3)*SUM(B28:B48)

Hope that helps a little... lol


S.

 

[playpbateman]

I would but it's on a separate sheet and that's what the sheet is named. I decided to go a slightly obscure way around it but figured it out. Thank you both for your help!