help on a nested countif/nested if formula

Hi,

I've had a good search for tips on how to complete this formula, but I haven't found anything which has solved my problem yet.

Basically, I have a table which contains several column and a list of tasks. When someone takes on a task, they put their initals in column G and when they complete a task, they put the date in column H. I'm trying to come up with a formla which counts the number of times a set of initials appears in the sheet, but not counting any tasks which are not completed (i.e. no date has yet been entered into column H for the corrosponding row). To throw another spanner into the works, some people have got into the habit of putting the "ongoing" in the date field. Rather then rely on people to keep this field empty, is there a way to put this condition into the formula also?

I've tried various ways, but the closed i think i've come so far is

=IF(AND('[Cleanups 2008.xls]April 2008'!$H:$H, NOT(("ongoing")OR ("")),COUNTIF('[Cleanups 2008.xls]April 2008'!$G:$G, "XX*"),"")

I know that this is technically wrong right now, etc, but that is basically what I would like to do. (The * is after the initial as a wild card as some people are putting spaces etc after their name, so I needed the formula to incorporate this).

Can anyone help me? It's starting to drive me a little nuts now.

cheers

vickie

How about?

Please Login or Register  to view this content.

this will count when column G is not blank and column H is not blank and is not "ongoing".

Note: Sumproduct() does not allow whole column references such as $G:$G unless you are using XL 2007.