This formula doesn't work anymore!

[Zakkala]

Hi guys,

People on this board helped me before Christmas come up with the following formula, and it worked like a dream. I've now gone back to my spreadsheet, tried it with some new data (in same layout) and it doesn't work!! Just gives zeros. Any ideas?

=SUMPRODUCT(--(Idle!$B$1:$B$1000=G$7),--(Idle!$A$1:$A$1000=$E11),Idle!$C$1:$C$1000)

Cheers,

Mel.

 

[shades]

Can you provide a link to the original post?

 

[smehnert]

What was the original aim of the formula - what are you trying to do?

 

[Zakkala]

Sorry, have lost the link to the original post - spent ages trying to find it.

What I'm trying to do is this.

Worksheet 2 has three columns of info. Column A contains names ('John Smith', 'Bob Jones' etc), Column B contains idle codes ('lunch', 'tea break' etc) and Column C contains times (01:45:07 etc).

On Worksheet 1 I have in column F a list of names and in row 8 the idle codes. I want a formula in each cell of the table to look at worksheet 2 and tell me how long each person has been in each idle code.

Hope that makes sense.

 

[shades]

Is this it?

http://www.mrexcel.com/board2/viewtopic.php?t=125463&highlight=

 

[Aladin Akyurek]

Hi guys,

People on this board helped me before Christmas come up with the following formula, and it worked like a dream. I've now gone back to my spreadsheet, tried it with some new data (in same layout) and it doesn't work!! Just gives zeros. Any ideas?

=SUMPRODUCT(--(Idle!$B$1:$B$1000=G$7),--(Idle!$A$1:$A$1000=$E11),Idle!$C$1:$C$1000)

Cheers,

Mel.

Two reasons how you can get 0...

1] Conditions simply do not hold.

2] The sum range Idle!$C$1:$C$1000 consists of text-formatted numbers.