# This formula doesn't work anymore!

#### Zakkala

##### Active Member
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.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Can you provide a link to the original post?

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

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.

Zakkala said:
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.

Replies
15
Views
527
Replies
2
Views
161
Replies
6
Views
202
Replies
1
Views
114
Replies
7
Views
308

1,203,380
Messages
6,055,098
Members
444,761
Latest member
lneuberger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back