Countif
 

I have two worksheets. I would like to have a formula that counts data
according to the following conditions.

Worksheet 1
A B C
02/15/2009 Keyword Keyword
01/14/2009 Keyword Keyword
01/31/2009 Keyword Keyword

Worksheet 2
A B C
January Total Keyword Total Keyword
February Total Keyword Total Keyword

I would like staff to be able to enter data into Worksheet 1 and have
Worksheet 2 automatically count the occurrences of the keyword in the given
month (January or February, etc.).

Countif
 

Try

=SUMPRODUCT((TEXT(Sheet1!$A$1:$A$100,"mmmm")=A1)*
(Sheet1!$B$1:$C$100="keyword"))

--
Jacob (MVP - Excel)


"jay" wrote:

I have two worksheets. I would like to have a formula that counts data
according to the following conditions.

Worksheet 1
A B C
02/15/2009 Keyword Keyword
01/14/2009 Keyword Keyword
01/31/2009 Keyword Keyword

Worksheet 2
A B C
January Total Keyword Total Keyword
February Total Keyword Total Keyword

I would like staff to be able to enter data into Worksheet 1 and have
Worksheet 2 automatically count the occurrences of the keyword in the given
month (January or February, etc.).

Countif
 

Jacob, this is working for some months but not others? I can't seem to find
out why. What exactly does the mmmm stand for?


=SUMPRODUCT((TEXT(Sheet1!$A$2:$A$750,"mmmm")=A6)*( Sheet1!$C$2:$C$750="assault on resident"))

"Jacob Skaria" wrote:

Try

=SUMPRODUCT((TEXT(Sheet1!$A$1:$A$100,"mmmm")=A1)*
(Sheet1!$B$1:$C$100="keyword"))

--
Jacob (MVP - Excel)


"jay" wrote:

I have two worksheets. I would like to have a formula that counts data
according to the following conditions.

Worksheet 1
A B C
02/15/2009 Keyword Keyword
01/14/2009 Keyword Keyword
01/31/2009 Keyword Keyword

Worksheet 2
A B C
January Total Keyword Total Keyword
February Total Keyword Total Keyword

I would like staff to be able to enter data into Worksheet 1 and have
Worksheet 2 automatically count the occurrences of the keyword in the given
month (January or February, etc.).

Countif
 

Thank you, Jacob. Ignore my other reply. I found extra spaces in the two
month columns that wouldn't work. You're the greatest!

"Jacob Skaria" wrote:

Try

=SUMPRODUCT((TEXT(Sheet1!$A$1:$A$100,"mmmm")=A1)*
(Sheet1!$B$1:$C$100="keyword"))

--
Jacob (MVP - Excel)


"jay" wrote:

I have two worksheets. I would like to have a formula that counts data
according to the following conditions.

Worksheet 1
A B C
02/15/2009 Keyword Keyword
01/14/2009 Keyword Keyword
01/31/2009 Keyword Keyword

Worksheet 2
A B C
January Total Keyword Total Keyword
February Total Keyword Total Keyword

I would like staff to be able to enter data into Worksheet 1 and have
Worksheet 2 automatically count the occurrences of the keyword in the given
month (January or February, etc.).