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.
|