Search results

jeffreyweir, thankyou for your feedback. I want to avoid VBA because no one else will understand it!

that worked! thankyou so much!

Thanks Hui but it's not exactly what I am after. The result of the formula finds and returns a value from multiple sheets. I am after a formula that returns the name of the sheet where the value is found instead of the value itself.

I did a search on the forum and couldn't find a solution. I have Employee Names across multiple sheets with each sheet representing a Division (sheet name). I also have an input sheet where I have a list of all Employees. In the input sheet I want a quick way to identify which...

Thankyou, I got it working as you suggested, I just moved the brackets a bit as per below. =SUMPRODUCT(($A$7:$A$100=C275)*($I$7:$I$100)*($L$7:$L$100))

Obviously I'm not logical enough! I tried this but it didn't work. =SUMPRODUCT(($A:$A=$C276))*($I:$I)*(L:L) Where column A searches for the dept code in cell C276 (180), Column I and Column L are the two columns I want to apply the Sumproduct to.

Thanks but not quite what I need. I need the formula to be able to change based on the change in department code. If I use the formula you suggested, it will always look for "100" but I need it to recognise the different department code. Noting that the different department codes have...

Hi I want to be able to run a SUMPRODUCT formula for changing criteria. I have different department codes for employees, example below. Dept Employee Salary Proportion Effort for Product A Effort for Product B 100 Jim 10% 70% 30% 100 Bill 20% 20% 80% 200 Peter 60% 30%...

Thankyou Hui and Harry for your comments. Harry - I do like pivot tables for summarising data but have found it a bit inflexible when it comes to reporting. I've begun to move away from pivot tables, and am using the Sumproduct formula more. Phil

Hi I wanted to use the sumproduct formula but with the ability to recognise when the criteria changed. See Example below. I'm using | as column separators. Cost Centre | Employee | Employee% | Employee Effort 100 | Bob | 50% | 30% 100 | Jim | 50% |...

Thankyou for your quick responses. I just couldn't see how there could be an error in the formula so I checked for errors in the data table. I found an error in row 4000 (about that row). Once I fixed the error my formula worked. I should have checked that earlier and not wasted your time...

I am having problems with my Sumproduct formula. If I type the following, it returns a value correctly. =SUMPRODUCT(($L$5:$DS$5=D$1434)*($I$7:$I$50=$B1441),$L$7:$DS$50) If I increase the data range, it returns a #N/A error. =SUMPRODUCT(($L$5:$DS$5=D$1434)*($I$7:$I$500=$B1441),$L$7:$DS$500)...