9 Replies Latest reply on Sep 8, 2016 6:32 AM by Peter Hanges

# Counting the number of times "X" appears in a column?

Hi all,

I have a rather simple question which I can't seem to figure out.  I have a column called Status, and there are three possible categories within the column: Won, Open, and Lost.  All I want to do is count the number of Open records within the column; in Excel it'd just be a CountIF statement.

Thanks for any help!

• ###### 1. Re: Counting the number of times "X" appears in a column?

Try creating the calculated field below, then show the sum of that field.

IF [Status] = "Open" THEN 1 ELSE 0 END

• ###### 2. Re: Counting the number of times "X" appears in a column?

This worked! Thanks so much.

Another quick add-on to that: Lets say I wanted to add multiple criteria (In this case Open + Won), how would I write that calculated field?

• ###### 3. Re: Counting the number of times "X" appears in a column?

The following should work:

IF ([Status] = "Open") OR ([Status] = "Won") THEN 1 ELSE 0 END

• ###### 4. Re: Counting the number of times "X" appears in a column?

If you want to continue with a calculated field then you could do something like IF [Status] = 'Open' or [Status] = 'Won' then 1 else 0

If these are just dimension values, you might be overthinking it and you could potentially use 'Number of Records' along with the dimension itself. I.e. lets say I have a dimension called region - drop that on the rows and the number of records on the columns; • ###### 5. Re: Counting the number of times "X" appears in a column?

Hi Tom,

The reason for the calculated field is because I'm going to be displaying it on a matrix with other calculated fields.  Thanks though!

• ###### 6. Re: Counting the number of times "X" appears in a column?

This worked! Thanks again.

• ###### 7. Re: Counting the number of times "X" appears in a column?

Hey Stephen I actually have one more related question for you if you don't mind.

I have a "Lead" column as well, where the value is numeric (Either a 1 or a 0).  I want to sum the total Leads where Status = "Open".  I tried using the above formula, but it gives me an error "Cannot compare integer and string values".  How would I go about doing this without changing my data type?

• ###### 8. Re: Counting the number of times "X" appears in a column?

Are you sure you want that in a calculated field? You could get the total number of leads for open statuses by added [Status] to your rows and SUM([Lead]) in the values area...

With that said, you could get a calculated field with the result by creating the following calculated field

IF Status = "Open" THEN [Lead] ELSE 0 END

Then looking at the sum.

• ###### 9. Re: Counting the number of times "X" appears in a column?

Understood, the reason for the calculated fields is because I'm building a matrix with a bunch of different metrics so I can't use Status as a row.  I actually figured it out just as you sent the reply, wrote:

IF [Lead]=1 AND [Status (Lead)] = "Open" THEN 1 ELSE 0 END

This seemed to work.  Thanks again for your help!