Geek Help: Excel formula
 

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.

Re: Geek Help: Excel formula
 

is it george best?

Re: Geek Help: Excel formula
 

Was he a mild mannered Janitor?

Re: Geek Help: Excel formula
 

You have pm!

Re: Geek Help: Excel formula
 

i think rodney ought to be given a written warning.

billy idols brother bone isnt he?

Re: Geek Help: Excel formula
 

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

Re: Geek Help: Excel formula
 

Is this answered now or not? why so secretive?

Re: Geek Help: Excel formula
 

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.

Re: Geek Help: Excel formula
 

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. :)

Re: Geek Help: Excel formula
 

i'd still get rid of rodney

Re: Geek Help: Excel formula
 

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

Re: Geek Help: Excel formula
 

Thought about doing it that way Mouse Trapper, thank you

Re: Geek Help: Excel formula
 

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.

Re: Geek Help: Excel formula
 

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.).

Re: Geek Help: Excel formula
 

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.