We help IT Professionals succeed at work.

formulating formula for 2 worksheets

davetough
davetough asked
on
Hello,
I am attaching excel sheet- hopefully i will be abe to explain:
formula is in (mechanic 2008).(cell B13)
i have two worksheets 'mechanic 2008 and company 2008.
you will see i have 75 % of formula working in mechanic 2008. cell B13.
( now I cannot make this work at all for the names jones and doe.)
I have 3 names adams, jones, and doe.
Formula:
(mechanic 2008).B13 = (company 2008).A1:A16( where = adams) * (company 2008.C1:C16 ( where = on) DIVIDED BY (mechanic 2008.B1)
Problems: I do not know how it is working to get only adams - the way I have it. and cannot get it to work for other names.
Part Cannot Figure out:
I also want to have formula in C13 ( - Only records where (company 2008).L1:L16 = 'new'

thank you.


formula1.xls
Comment
Watch Question

Most Valuable Expert 2011
Awarded 2010

Commented:
Hello davetough,

I think you are referencing the wrong cell ranges

try this and copy down

=SUMPRODUCT(('mechanic 2008'!$A13='company 2008'!$A$1:$A$100)*('company 2008'!$C$1:$C$100="on"))/B1

cheers, teylyn

Author

Commented:
thank you - now is there a way I can have C13 display only where field L is 'new' in (company 2008 worksheet) thank you
Most Valuable Expert 2011
Awarded 2010

Commented:
do you mean you want to count only the items that have 'new' in column L?

If so, you need to add another parameter to the sumproduct that compares column L with 'new'

=sumproduct((param 1)*(param 2)*('sheet'!$L$1:$L$100="new"))/B1

Adjust the sheet name and the ranges. I can't see the original formula on my mobile while typing the comment.
Most Valuable Expert 2011
Awarded 2010
Commented:
Here it is in full

=SUMPRODUCT(('mechanic 2008'!$A13='company 2008'!$A$1:$A$100)*('company 2008'!$C$1:$C$100="on")*('company 2008'!$L$1:$L$100="new"))/B1

cheers, teylyn

Author

Commented:
thank you greatly-works -
Perfect!!
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks for the grade!