asked on

formulating formula for 2 worksheets

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

davetough

ASKER

thank you - now is there a way I can have C13 display only where field L is 'new' in (company 2008 worksheet) thank you

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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.

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davetough

ASKER

thank you greatly-works -
Perfect!!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Thanks for the grade!