Including FIND in COUNTIF

04/17/19 Edited 12/09/19

I would like to use COUNTIFS when two conditions are true

  1. The value in College = "ED"
  2. The text [Development Category] contains the word "Build".

EXAMPLES THAT COUNT:

College = "ED", [Development Category] = "Build OL only"

College = "ED", [Development Category] = "Build BL only"

EXAMPLES NOT TO COUNT:

College = "ED", [Development Category] = "Major rebuild"

College = "PS", [Development Category] = "Minor rebuild"

The formula would count all instances within a range (say rows 1 to 10):

=COUNTIF(College$1:College$10, "ED", [Development Category]$1:[Development Category]$10, FIND("Build", [Development Category](@cell))>1)

I'm getting a #UNPARSEABLE error, which I am sure has to do with the FIND part of the formula. It should look at the text in [Development Category], and if the text contains the word "Build" (i.e., FIND value is greater than 1), AND value of College = "ED", it should add an instance.

I am not sure if this is possible, and them I am not sure if I am evaluating the text in the [Development Category] correctly.

Thanks!

 

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Actually the issue is with your @cell reference. It does not need its own set of parenthesis nor does it need a column reference. The column reference is designated by your range.

     

    Original:

    FIND("Build", [Development Category](@cell))

    Adjusted:

    FIND("Build", @cell)

    thinkspi.com

Sign In or Register to comment.