COMPUTING TWO COLUMNS
 

I have in column A the date. In column B I have multiple text which shows
what was accomplished or occurred during the month.
Example:
A B
11/10/2005 Repair, Holiday, etc.

I want to count the text individually for each month. I know how to do it
for one column, =COUNTIF(N31:N1006,"*Warranty*"), but not two columns
combined.

COMPUTING TWO COLUMNS
 

Hi!

Are the dates all in the same year?

If so try this:

Use a cell to hold the date/month of interest. Enter the date using the the
first day of that month:

C1 = 11/1/2005

Use a cell to hold the text value that you want to count:

D1 = Warranty

Then:

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C1)),--(ISNUMBER(SEARCH(D1,B1:B10))))

If the dates span multiple years then again, enter the date of interest in
C1.

Then:

=SUMPRODUCT(--(A1:A10=C1),--(A1:A10<=DATE(YEAR(C1),MONTH(C1)+1,0)),--(ISNUMBER(SEARCH(D1,B1:B10))))

Biff

"roy.okinawa" wrote in message
...
I have in column A the date. In column B I have multiple text which shows
what was accomplished or occurred during the month.
Example:
A B
11/10/2005 Repair, Holiday, etc.

I want to count the text individually for each month. I know how to do it
for one column, =COUNTIF(N31:N1006,"*Warranty*"), but not two columns
combined.

COMPUTING TWO COLUMNS
 

Biff,

The long holiday weekend took me away from my computer.

The formula you provided works, however, I need something that will sort
through the multiple text I have in column B without listing each word
separately. Is there a way to do multiple COUNTIF or ??? in one formula, so
I can count Column A and B together?

Example:

Column A Column B

11/10/2005 Warranty, Repair
11/10/2005 Repair
11/12/2005 Holiday, Warranty, Repair

Nov Repair = 3
Nov Warranty = 2


Thanks,

Roy
"Biff" wrote:

Hi!

Are the dates all in the same year?

If so try this:

Use a cell to hold the date/month of interest. Enter the date using the the
first day of that month:

C1 = 11/1/2005

Use a cell to hold the text value that you want to count:

D1 = Warranty

Then:

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C1)),--(ISNUMBER(SEARCH(D1,B1:B10))))

If the dates span multiple years then again, enter the date of interest in
C1.

Then:

=SUMPRODUCT(--(A1:A10=C1),--(A1:A10<=DATE(YEAR(C1),MONTH(C1)+1,0)),--(ISNUMBER(SEARCH(D1,B1:B10))))

Biff

"roy.okinawa" wrote in message
...
I have in column A the date. In column B I have multiple text which shows
what was accomplished or occurred during the month.
Example:
A B
11/10/2005 Repair, Holiday, etc.

I want to count the text individually for each month. I know how to do it
for one column, =COUNTIF(N31:N1006,"*Warranty*"), but not two columns
combined.

COMPUTING TWO COLUMNS
 

Biff,

Belay my last email.

I needed to take a step back and look at my worksheet. Your formula is
exactly what I needed. The text is going to be individually titled in other
columns anyway.

Thanks,

Roy

"Biff" wrote:

Hi!

Are the dates all in the same year?

If so try this:

Use a cell to hold the date/month of interest. Enter the date using the the
first day of that month:

C1 = 11/1/2005

Use a cell to hold the text value that you want to count:

D1 = Warranty

Then:

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C1)),--(ISNUMBER(SEARCH(D1,B1:B10))))

If the dates span multiple years then again, enter the date of interest in
C1.

Then:

=SUMPRODUCT(--(A1:A10=C1),--(A1:A10<=DATE(YEAR(C1),MONTH(C1)+1,0)),--(ISNUMBER(SEARCH(D1,B1:B10))))

Biff

"roy.okinawa" wrote in message
...
I have in column A the date. In column B I have multiple text which shows
what was accomplished or occurred during the month.
Example:
A B
11/10/2005 Repair, Holiday, etc.

I want to count the text individually for each month. I know how to do it
for one column, =COUNTIF(N31:N1006,"*Warranty*"), but not two columns
combined.