# Formula to calculate % based on a number & text in cells

1. ## Formula to calculate % based on a number & text in cells

Morning all, first post here.

I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.

Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.

So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.

I realise it would be easier for you to see the database so if you email me or PM me that's fine.

Regards,
Conor

2. ## Re: Formula to calculate % based on a number & text in cells

COUNTIF will be the formula to use. So it would be something like =Countif("A:A","100")/counta("A:A")

3. ## Re: Formula to calculate % based on a number & text in cells

Originally Posted by nathansav
COUNTIF will be the formula to use. So it would be something like =Countif("A:A","100")/counta("A:A")
Thanks Nathan,

I will use that however can the 'countif' be set to recognise a date or 8 characters? (i.e. 11.05.14). I have attached the file in question.

4. ## Re: Formula to calculate % based on a number & text in cells

Sorry i'm a bit lost now, what is it you want doing and where on the sheet?

5. ## Re: Formula to calculate % based on a number & text in cells

Originally Posted by nathansav
Sorry i'm a bit lost now, what is it you want doing and where on the sheet?
Sorry I uploaded the wrong file! See attached.

6. ## Re: Formula to calculate % based on a number & text in cells

It will be a different type of formula an array formula, requires ctrl + shift + enter, but would be something like this

=SUM(IF(LEN(A1:A6)>=8,1,0))/COUNTA(A1:A6) where a1:a6 is the data range.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1