How do you count data that matches more than one condition? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
ryesworld 11/8/2005 11:29 AM PST
  Question
  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".
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Umlas 11/8/2005 11:40 AM PST
   
  =SUMPRODUCT(--(Range={"text string A","text string B"}))
Bob Umlas
Excel MVP

"ryesworld" <ryesworld@discussions.microsoft.com> wrote in message
news:3F69E78D-C851-49C4-BF8E-755852609135@microsoft.com...
> 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".


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Biff 11/8/2005 11:45 AM PST
   
  Hi!

Try one of these:

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

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

Biff

"ryesworld" <ryesworld@discussions.microsoft.com> wrote in message
news:3F69E78D-C851-49C4-BF8E-755852609135@microsoft.com...
> 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".


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
RagDyer 11/8/2005 1:28 PM PST
   
  Another way:

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

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


"ryesworld" <ryesworld@discussions.microsoft.com> wrote in message
news:3F69E78D-C851-49C4-BF8E-755852609135@microsoft.com...
> 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".

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
ryesworld 11/8/2005 3:01 PM PST
   
  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" <ryesworld@discussions.microsoft.com> wrote in message
> news:3F69E78D-C851-49C4-BF8E-755852609135@microsoft.com...
> > 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".
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
RagDyer 11/8/2005 4:55 PM PST
  Answer
  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" <ryesworld@discussions.microsoft.com> wrote in message
news:C0D21DD5-487C-4270-AB44-125AA2CEEFE4@microsoft.com...
> 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" <ryesworld@discussions.microsoft.com> wrote in message
> > news:3F69E78D-C851-49C4-BF8E-755852609135@microsoft.com...
> > > 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".
> >
> >

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nicole 3/22/2009 6:52 PM PST
   
  how do i count a value with less than 1 days in a different columns???

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jarek Kujawa 3/22/2009 7:34 PM PST
   
  =COUNTIF(your_range;"<"&1)


U¿ytkownik "Nicole" <Nicole@discussions.microsoft.com> napisa³ w wiadomo¶ci
news:E3C32D30-F51A-4406-A394-6180EDA43183@microsoft.com...
> how do i count a value with less than 1 days in a different columns???
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jarek Kujawa 3/22/2009 7:41 PM PST
   
  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" <Nicole@discussions.microsoft.com> napisa³ w wiadomo¶ci
news:E3C32D30-F51A-4406-A394-6180EDA43183@microsoft.com...
> how do i count a value with less than 1 days in a different columns???
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jarek Kujawa 3/22/2009 7:44 PM PST
   
  yet another way

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




U¿ytkownik "Nicole" <Nicole@discussions.microsoft.com> napisa³ w wiadomo¶ci
news:E3C32D30-F51A-4406-A394-6180EDA43183@microsoft.com...
> how do i count a value with less than 1 days in a different columns???
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies