Hi Guys,
Really struggling with a query in excel.
I am trying to use SUMIF to count the occurrences of a 3 character alphanumeric entry of text in a series of cells.
I have a simple version which works however it counts the cells which have that occurrence in not the amount of times that string occurs.
I know it would be better to rewrite the spreadsheet but that is not possible due to certain limitations.
In this example the text in the field is
"UH X4 - 314657 R01 22/02/09 - 314972 R09 03/05/09 - 315627 R01 09/06/09 - 315932 R09 25/06/09"
This is telling me
UH = Unacceptable history
X4 = 4 Jobs have been done to this job
xxxxxx = the job number
XYY = The person working on it
Date, the date the job was completed
I have about 30 other rows similar to this within the column and I need to count all the times R01 and R02 occur in the whole column, not the ammount of cells that occurrence is in.
This is what I am using to cound that column.
=SUM(COUNTIF($N$6:$N$1000,"*R01*"))
This calculates how many Cells R01 appears in however as you can see above R01 appears twice, I need it to count that as 2 not 1.
Also if there is some way I can extract the dates from this field and calculate a mean average between the dates and flag up dates that are 3 weeks apart or less that would be great.
Any help would be appreciated, I'm sorry for rambling on but I'm really stuck on this one.
Thanks
Ross
Bookmarks