Geek Help: Excel formula

[Viney]

Okies im trying to make a new speadsheet for work.
Contained in the spead sheet are 10 columns, 2 of which are Operatives name and Job completed.

What im trying to do, is calculate how many jobs have and havent been completed for each Operative.

Example

Operative---Job Completed
Dave--------- --Yes
Mark----------- Yes
Sue------------ No
Rodney--------- No
Rodney--------- No
Sue ------------Yes

The table i want to create

Name--Job Completed--Job Not completed

Dave --------1 ------------------0
Mark --------1------------------- 0
Sue ---------1 -------------------1
Rodney ------0 -------------------2

The spread sheet will be a live sheet, and the names, and yes/No will be in drop down boxes.

I have been using =count(if(G3:G1000=Dave)*(K3:K1000=Yes) but this will not work and i cant work out why. Any help greatfully recieved.

[keithd]

is it george best?

[Viney]

Was he a mild mannered Janitor?

[thor]

You have pm!

[keithd]

i think rodney ought to be given a written warning.

billy idols brother bone isnt he?

[Oli]

I hate excel, though have to use it all day. I much prefer powerpoint, pretty but useless. There's a fable in there somewhere.

[Stu]

You have pm!
Is this answered now or not? why so secretive?

[Flamin_Squirrel]

Confused. First, why there's an 'if' in the formula?

Also, are the remaining columns listing the different jobs, with yes/nos in them? Otherwise I'm wondering how you could get Rodney with 2 uncomplete jobs if theres only a choice between complete and incomplete.

[Viney]

Confused. First, why there's an 'if' in the formula?

Also, are the remaining columns listing the different jobs, with yes/nos in them? Otherwise I'm wondering how you could get Rodney with 2 uncomplete jobs if theres only a choice between complete and incomplete.

Its the formula that the Excel help says woudl give me the answer, but its not!

The last column on the sheet is Notes.

The sheet is an ongoing sheet that at the end of the month, i will need to know if the work is complete or not, then theres a notes column after that.

Anyhoo, Thor is on the case, Thanks Thor. :)

[keithd]

i'd still get rid of rodney

[Mousetrapper]

Hi Viney,

This might be a bit of a long way round as it means creating another column.

1. Create extra column next to the Yes and Concatenate the name and yes /no. i.e. =a2&b2, this would give "DaveYes"

2. In the second table put the formula for job completed
=COUNTIF($c$3:$c$9,I4&"Yes")

The cell I4 has Dave in.

3. Do similar for the "No" column.

Probably a more elegant way that doesn't involve an extra column. If there is please post.


Hope that helps,

Mousetrapper

[Viney]

Thought about doing it that way Mouse Trapper, thank you

[Mousetrapper]

Also if it's going to be a live sheet just put the whole column on i.e. C:C rather than specifying a range.

[fubber]

Sounds like you need to invoke an Array Formula. See Excel help on this...

(array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.).

[Mousetrapper]

Ah yes, an array formula and something similar to the one you suggested appears to work

{=SUM(IF(A4:A1000=D4,1,0)*(IF(B4:B1000="yes",1,0)))}

If you edit the formula without CTRL+SHIFT+ENTER you lose the array.