Count occurances of multiple arguments
 

undefined

How do you count occurances of multiple arguments?

For instance, determine how may times 'XX' or 'YY' occurs in column A?

Or determine how many times 'AA' occurs in column A with 'BB' in column
B?


Also, is there a way to keep cells with blank spaces from being
included when using COUNTA?

Thanks,

Alan Smith


--
TARS1
------------------------------------------------------------------------
TARS1's Profile: http://www.excelforum.com/member.php...o&userid=27079
View this thread: http://www.excelforum.com/showthread...hreadid=465969

You can count occurences of XX in col A, for instance, with this
formula:
=COUNTIF(A1:A10,"XX")

Count occurences of AA in col A with BB in col B with this:
=SUMPRODUCT(--(A1:A10="AA"),--(B1:B10="BB"))

COUNTA() counts non-blank cells, and a cell with a space in it is
non-blank, to Excel. If you are sure that the cells contain only one
space, you could use this formula:
=COUNTA(A1:A10)-COUNTIF(A1:A10," ")
That counts your non-blanks and nets out the cells with one space.

You could use:

=sumproduct(--(a1:a10="xx"),--(b1:b10="yy"))

=sumproduct() likes to work with numbers. the -- stuff converts true and false
to 1 and 0.

=counta()

will ignore empty cells--really empty cells. But it will include formulas that
evaluate to "" (so they look blank). It'll even include those if they've been
converted to values (copy|paste special|values)

One way:

=SUMPRODUCT(--(A1:A10<""))



TARS1 wrote:

undefined

How do you count occurances of multiple arguments?

For instance, determine how may times 'XX' or 'YY' occurs in column A?

Or determine how many times 'AA' occurs in column A with 'BB' in column
B?

Also, is there a way to keep cells with blank spaces from being
included when using COUNTA?

Thanks,

Alan Smith

--
TARS1
------------------------------------------------------------------------
TARS1's Profile: http://www.excelforum.com/member.php...o&userid=27079
View this thread: http://www.excelforum.com/showthread...hreadid=465969

--

Dave Peterson

In article ,
TARS1 wrote:

...determine how may times 'XX' or 'YY' occurs in column A?

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,{"XX","YY"},0))))

or

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A100,B1:B2,0))))

....where B1 contains your first criterion, such as XX, and B2 contains
your second criterion, such as YY.

Hope this helps!

To All: I tried both formulas trying to count how many times word " xxxxx" is
mentioned in the sentences that are from column d5 to d300. all that I am
getting is zero.
example: today we will be having xxxxx.
today we will be having yyyyy.
today we will be having zzzzz.
today we will be having xxxxx.

or is there another formula that I should be using ?

"Dave O" wrote:

You can count occurences of XX in col A, for instance, with this
formula:
=COUNTIF(A1:A10,"XX")

Count occurences of AA in col A with BB in col B with this:
=SUMPRODUCT(--(A1:A10="AA"),--(B1:B10="BB"))

COUNTA() counts non-blank cells, and a cell with a space in it is
non-blank, to Excel. If you are sure that the cells contain only one
space, you could use this formula:
=COUNTA(A1:A10)-COUNTIF(A1:A10," ")
That counts your non-blanks and nets out the cells with one space.

Thanks Domenic thats what I needed..

"Domenic" wrote:

To count the number of times a word appears at the end of a text string
for each cell, try...

=COUNTIF(D5:D300,"*xxxxx")

To count the number of times a word appears anywhere within a text
string for each cell, try...

=COUNTIF(D5:D300,"*xxxxx*")

Hope this helps!

In article ,
Art wrote:

To All: I tried both formulas trying to count how many times word " xxxxx" is
mentioned in the sentences that are from column d5 to d300. all that I am
getting is zero.
example: today we will be having xxxxx.
today we will be having yyyyy.
today we will be having zzzzz.
today we will be having xxxxx.

or is there another formula that I should be using ?

Count occurances of multiple arguments
 

I tried Dave O's formula and it's working great for me but it is still
counting filtered or hidden data, any way to correct this?


--
tz81c1
------------------------------------------------------------------------
tz81c1's Profile: http://www.excelforum.com/member.php...o&userid=36538
View this thread: http://www.excelforum.com/showthread...hreadid=465969