Multiple criteria options in a formula

[ThomasCarter]

Currently using ({5,"*5*"}) to search for numbers and text that contain 5.

Curious how I can make it look for 5 or 6. So if any of the cells in the range contain either 5 or 6, it will work.

[Pete_UK]

Try this:

{5,"*5*",6,"*6*"}

although it does depend on the functions you are using for the search (I imagine this is still the COUNTIF problem from earlier).

Hope this helps.

Pete

[ThomasCarter]

This was for a SUMIF problem actually, but I had no idea it differed depending on the function.

Interestingly, this sums the 1's as expected but doesn't seem to sum the 7's but also does not bring up an error.

Full code:

Please Login or Register  to view this content.

Edit: I am currently using the temporary solution of using 2 seperate cells to count 1's and 7's and then just adding the results of these 2 cells together. This works, but it's obviously not ideal.

[Palmetto]

I don't think you can use an array of values like that in SUMIF.

You could try this array formula.

{=SUM(IF(ISNUMBER(SEARCH({1,7},A4:A999)),B4:B999))}

Array formulas are entered using the key combination of Ctrl+Shift+Enter.
Excel automatically adds the curly brackets.

[Ace_XL]

I don't think you can use an array of values like that in SUMIF.

You probably can. Just enclose the SUMIF in a SUM function. Hence,

=SUM(SUMIF(A4:A999,({1,"*1*",7,"*7*"}),B4:B999))

[Palmetto]

My comment was related strictly to using such an array within SUMIF - not a nested formula as you show.
That array will not work in SUMIF.

Good job on the formula. I did not think of nesting SUM approach.