Hi All:
I need a formula that will count the number of occurences that a column
contains either "text string A" or "text string B".

=SUMPRODUCT(--(Range={"text string A","text string B"}))
Bob Umlas
Excel MVP

"ryesworld" wrote in message
...
Hi All:
I need a formula that will count the number of occurences that a column
contains either "text string A" or "text string B".

Hi!

Try one of these:

=COUNTIF(A1:A10,"textA")+COUNTIF(A1:A10,"textB")

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{"textA","textB"},0))))

Biff

"ryesworld" wrote in message
...
Hi All:
I need a formula that will count the number of occurences that a column
contains either "text string A" or "text string B".

Another way:

=SUM(COUNTIF(A:A,{"aa","bb"}))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
Hi All:
I need a formula that will count the number of occurences that a column
contains either "text string A" or "text string B".

Hi RagDyer...

I'd like to use your formula, "=SUM(COUNTIF(Sheet1!E1:E20,{"aa","bb"}))",
with your other formula from my other posting,
"=SUMPRODUCT((Sheet1!A1:A20=C1)*(Sheet1!B1:B20=D1) )". In other words,
Sheet1!B1:B20=D1 or D2. (D1 is "aa" and D2 is "bb"). A number count should
only be retuned if all data ranges are true. (ie: if a zero was retuned from
either formula, zero will be the answer)

I hope that makes sense....

"RagDyer" wrote:

Another way:

=SUM(COUNTIF(A:A,{"aa","bb"}))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
Hi All:
I need a formula that will count the number of occurences that a column
contains either "text string A" or "text string B".

Don't know if I quite follow you.

Is this what you're looking for:

=SUMPRODUCT((Sheet1!A1:A20=C1)*(Sheet1!B1:B20={"aa ","bb"}))

OR, do you want to reference particular cells, instead of hard coding the
actual text:

=SUMPRODUCT((Sheet1!A1:A20=C1)*((Sheet1!B1:B20=D1) +(Sheet1!B1:B20=D2)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
Hi RagDyer...

I'd like to use your formula, "=SUM(COUNTIF(Sheet1!E1:E20,{"aa","bb"}))",
with your other formula from my other posting,
"=SUMPRODUCT((Sheet1!A1:A20=C1)*(Sheet1!B1:B20=D1) )". In other words,
Sheet1!B1:B20=D1 or D2. (D1 is "aa" and D2 is "bb"). A number count
should
only be retuned if all data ranges are true. (ie: if a zero was retuned
from
either formula, zero will be the answer)

I hope that makes sense....

"RagDyer" wrote:

Another way:

=SUM(COUNTIF(A:A,{"aa","bb"}))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ryesworld" wrote in message
...
Hi All:
I need a formula that will count the number of occurences that a
column
contains either "text string A" or "text string B".

how do i count a value with less than 1 days in a different columns???

=COUNTIF(your_range;"<"&1)


U¿ytkownik "Nicole" napisa³ w wiadomo¶ci
...
how do i count a value with less than 1 days in a different columns???

another way:

=SUM(IF((NOT(ISBLANK(your_range)))*(your_range<1), 1,))

CTRL+SHIFT+ENTER this formula as it is an array-formula, after having done
it correctly the formula will show with curly brackets
{=SUM(IF((NOT(ISBLANK(your_range)))*(your_range<1) ,1,))}

also pls replace ";" with "," in my previous formula to look like this:

=COUNTIF(your_range,"<"&1)



U¿ytkownik "Nicole" napisa³ w wiadomo¶ci
...
how do i count a value with less than 1 days in a different columns???

yet another way

=SUMPRODUCT(NOT(ISBLANK(your_range))*(your_range<1 ))




U¿ytkownik "Nicole" napisa³ w wiadomo¶ci
...
how do i count a value with less than 1 days in a different columns???