Which Function should I use

**Metrobus425** · 05-31-2012, 01:38 PM

I need a function something like =SUM(AB6)IF(F6:F6,"Complete") to show a number only if the work is complete (otherwise return a zero or blank)

I have achieved a week for a site visit based on the row date, in the column AB so I can then count how many sites were visited each week and also get averages etc. However in the adjacent column I want to return the same week number but only if the row information in column F6 says complete. I cannot seem to find anything to do this.

David

**amotto11** · 05-31-2012, 01:43 PM

David,

I am not sure if i totally understand your question but would you need something like this =If(F6="Complete",Sum(AB6),0)??

**dilipandey** · 05-31-2012, 01:48 PM

HI Metrobus425,

Suggest you to change the thread title to reflect the query being asked.. see the forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey

<click on below 'star' if this helps>

**Metrobus425** · 05-31-2012, 02:10 PM

Found it

=SUM($AB6)*COUNTIF($F7:$F7,"Complete")
=SUM($AB6)*COUNTIF($F7:$F7,"Cancelled") etc

it was the * that I needed!

Thanks if you have already started to look for an answer, sorry to waste your time

**zbor** · 05-31-2012, 02:14 PM

Well, you should change title anyway as dilipandey ask you to...

That would ensure you get answer quicklier in the future and avoid inconvenience that might happend.

Please read Rule 1 (at least) of the Forum rules.

**Metrobus425** · 05-31-2012, 02:15 PM

Sorry, should have looked for replies before I posted my own found answer

amotto11 Thank you. I will try that as an alternative and see if it works

dilipandey Will do but that title came up on auto fill (must have used it previously without comment) and I thought it was exactly appropriate. Hadn't realised there were subject text rules!

David

**Metrobus425** · 05-31-2012, 02:22 PM

I have apologised, shall remember for the future and fully understand the need for concise subject details. Of course to change it now would seem somewhat too late especially if retaining it discourages anyone to waste their time reading an already answered enquiry.

Presumably there will be no further comments made.

Not so sure quicklier is a word though. It is certainly underlined in red on my computer

David