count
 

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

Try
=SUMPRODUCT((TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")) *B1:B100)

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

Try this:

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C2)),--(YEAR(A1:A10)=YEAR(C2)),--(B1:B10<""))

--
Biff
Microsoft Excel MVP


"Mona" wrote in message
...
I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I
have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2.
Thanks! -Mona

count
 

Hi,

Try this. "Sometext" is the String your looking for in column B

=SUMPRODUCT((TEXT(A1:A1000,"mmyy")=TEXT(C2,"mmyy") )*(B1:B1000="Sometext"))


Mike

"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

I tried and I get a "#value". I am trying to count the text in colB or
simply count each cell. Thanks

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")) *B1:B100)

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

Try the below
=SUMPRODUCT(--(TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")),--(B1:B100<""))

=SUMPRODUCT(--(TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")))

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I tried and I get a "#value". I am trying to count the text in colB or
simply count each cell. Thanks

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")) *B1:B100)

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

I should have added if you want to count anything in column B use this

=SUMPRODUCT((TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")) *(B1:B100<""))

Mike

"Mike H" wrote:

Hi,

Try this. "Sometext" is the String your looking for in column B

=SUMPRODUCT((TEXT(A1:A1000,"mmyy")=TEXT(C2,"mmyy") )*(B1:B1000="Sometext"))


Mike

"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona

count
 

Thank you Jacob! This works!

"Jacob Skaria" wrote:

Try the below
=SUMPRODUCT(--(TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")),--(B1:B100<""))

=SUMPRODUCT(--(TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")))

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I tried and I get a "#value". I am trying to count the text in colB or
simply count each cell. Thanks

"Jacob Skaria" wrote:

Try
=SUMPRODUCT((TEXT(A1:A100,"mmyy")=TEXT(C2,"mmyy")) *B1:B100)

If this post helps click Yes
---------------
Jacob Skaria


"Mona" wrote:

I have daily dates (mm/dd/yyyy) in colA and text in colB. In cell C2 I have
03/2008 (mm/yy). I need a formula : count colB when A:A=C2. Thanks! -Mona