CODEWITHSUNDEEP
excelexcel-formula
Seth
Seth

Reputation: 334

Get a count of X in a column that has X and Y

I have a column that has values of X and Y. I want to write a formula in excel that can calculate the number of (x)s and (y)s in the column. Screen Shot

I've tried using the IF() formula but haven't had much sucess.

Upvotes: 2

Views: 1321

Answers (4)

Richard Vivian
Richard Vivian

Reputation: 1750

Just for a variance in the approach, this can be done with an array formula. (Countif is more efficient.)

=SUM(IF(A2:A8="X",1,0))

In order to let excel know this is an array formula you need to press ctrl-shift-enter after adding the formula.

Your formula bar should then look like this:

{=SUM(IF(B13:B19="X",1,0))}

The braces "{" and "}" before and after are added by excel automatically.

Array formula can be used to allow normal Excel formula that work on a cell to be applied to a range of cells.

Some references:

http://www.mrexcel.com/articles/CSE-array-formulas-excel.php

http://www.dummies.com/how-to/content/how-to-build-an-array-formula-in-excel-2010.html

Upvotes: 1

Marcel
Marcel

Reputation: 2794

you can use the COUNTIF function:

COUNTIF(range, criteria)

you can learn more about it here

so in your example, you can use it like this: 

=countif(A:A,"X")

to find the number of X and 

=countif(A:A,"Y")

to find the number of Y.

Upvotes: 1

DCX
DCX

Reputation: 117

=COUNTIF(B:B,"Y") same again for X

Upvotes: 0

Preuk
Preuk

Reputation: 632

Your formula should be something like that for D3 :

=COUNTIF(A2:A100, "X")

and for E3 :

=COUNTIF(A2:A100, "Y")

Upvotes: 2

Related Questions