View Single Post
  #4   Report Post  
roy.okinawa
 
Posts: n/a
Default 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.