Count occurances of multiple arguments

  • Thread starter Thread starter TARS1
  • Start date Start date
T

TARS1

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
 
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 said:
...determine how may times 'XX' or 'YY' occurs in column A?
Click to expand...

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 ?
 
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!
 
Thanks Domenic thats what I needed..

Domenic said:
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!
Click to expand...
 
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?
 
Back
Top