3 Replies Latest reply on Aug 8, 2013 11:49 AM by Kerith Gaines

# count x value but exclude similar x value

Hey Everyone!

I'm scratching my head over setting up the count logic for this. I need to count the number of times "Blue" shows up in a column, and I need to exclude "Dark-Blue" - I tried the following:

if contains ([Color], "Blue") then "1"

elseif contains ([Color], "Dark-Blue") then "0"

end

But because Dark-Blue still has "Blue" in it, the Dark-Blue values are still being counted by the first if statement.

How do I exclude Dark-Blue from the count?

Thank you so much for your help!

• ###### 1. Re: count x value but exclude similar x value

Are "Blue" and "Dark-Blue" part of a larger string?  If not, and the colors are the precise value of a string, then you shouldn't use Contains().  Instead, you'd simply want:

IF [Color] = "Blue" then 1

elseif [Color] = "Dark-Blue" then 0

end

If these values are part of a larger String, then Johan's method should work.  If you still need help, post a packaged workbook, along with a description of what you are trying to accomplish.

• ###### 2. Re: count x value but exclude similar x value

Yes, it is part of a larger string with other color values.

Ok - so I finally have a formula that will work - thought I would post it here in case anyone else gets stumped on this.

IF Contains ([Color], "Blue")

AND Contains ([Color], "Dark")=False then 1

Else 0

End

Phew

Thanks for the suggestions everyone!!

• ###### 3. Re: count x value but exclude similar x value

So I spoke too quickly - here is the formula that did finally work:

IF Contains([Color], “Red”) then 0
ELSEIF Contains([Color], “Green”) then 0
ELSEIF Contains ([Color],”Dark”) then 0
ELSE 1
End

Thanks again for the suggestions, and I will mark this as correct now