determining multiple cell contents and proving a count

**m50b25** · 07-29-2013, 10:41 AM

Hi,

I've nearly got this covered after looking many examples.

This is where I am:

=SUM(IF(ISNUMBER(FIND("Commission",D452)),"1","0")+(IF(E452>Markets!$C$2,"1",))+(IF(ISNUMBER(COUNTIF(F452,Markets!$C$3:Markets!$E$3)),"0","1")))

Basically, the third section (Markets!$C$3:Markets!$E$3) doesnt seem to work. If I change that to just (Markets!$C$3) then it works for the one keyword.

What this section is supposed to do, is return a 0 if cell F452 contains any of the keywords in cells Markets!$C$3:Markets!$E$3.

Any help greatly appreciated!

**ARGK** · 07-29-2013, 12:28 PM

Hi
Try changing from:

Markets!$C$3:Markets!$E$3
to
Markets!$C$3:$E$3
Good luck.
Tony

**m50b25** · 07-30-2013, 04:04 AM

thanks tony but that didn't work unfortunately.

Here is what I am trying to do:

I have three cells that when they all meet a criteria, the 4th cell displays 1. If one of the cells doesn't meet the criteria then it should display 0.

The formula I've done so far (above) gives each cell either a 1 or 0. These are then added together in that long formula and 4th cell displays 0, 1, 2 or 3.

If you can think of a better way, I'd love to hear it!

many thanks
Steve

**XOR LX** · 07-30-2013, 04:24 AM

Hi,

Replace:

IF(ISNUMBER(COUNTIF(F452,Markets!$C$3:Markets!$E$3)),"0","1")

with:

IF(SUM(--ISNUMBER(SEARCH(Markets!$C$3:$E$3,Sheet1!F452)))>0,0,1)

Your entire formula will now need to be array-entered (confirm with CTRL+SHIFT+ENTER, not just ENTER).

Regards