Two variables

[Guest]

I hope someone can help!

I have a number of workbooks with various staff data including staff costs
and proportion of time worked and cost centre by individual employee in one
worksheet and need to do some analysis of this based on staff category in
another sheets withinh the individual workbooks.

I have proportion in column "N", category in "F" and cost centre in "M".

Cost centres are in the format C********* and other cost centres start with
either H or O

The info has to be reported against staff category and cost centres (split
between "C" and others).

For example a list of ten staff may have three different categories and two
different cost centres.

I've tried IF and SUMIF using wildcards but can't get it to look at the two
variables ie the category and cost centre.



I hope this makes sense and any ideas gratefully received.

 

[JulieD]

Hi

you'll probably need to use sumproduct for this

e.g.
=SUMPRODUCT(--(F1:F30="category1"),--(M1:M30="cost_centre1"),--(N1:N30))

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more details

Hope this helps
Cheers
JulieD

 

[Bob Phillips]

Something like

=SUMPRODUCT(--(LEFT(M1:M1000,1)="C"),--(M1:M100="ABC"))

which counts the number in category ABC for cost centres starting with C.

If you want to sum another column, use

=SUMPRODUCT(--(LEFT(M1:M1000,1)="C"),--(M1:M100="ABC"), L1:L100)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, that should be

=SUMPRODUCT(--(LEFT(M1:M1000,1)="C"),--(F1:F100="ABC"))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Julie,

You don't need the -- on the N1:N30, and although it doesn't cause a
problem, it is better IMO not to have it so as to separate and differentiate
the Boolean conditions from the summing range (as well as avoid an
unnecessary operation).

Regards

Bob

 

[JulieD]

Hi Bob

thanks for this ...

Cheers
JulieD

Hi Julie,

You don't need the -- on the N1:N30, and although it doesn't cause a
problem, it is better IMO not to have it so as to separate and
differentiate
the Boolean conditions from the summing range (as well as avoid an
unnecessary operation).

Regards

Bob

 

[Guest]

Thanks for your help. Problem solved.


David

Hi

you'll probably need to use sumproduct for this

e.g.
=SUMPRODUCT(--(F1:F30="category1"),--(M1:M30="cost_centre1"),--(N1:N30))

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more details

Hope this helps
Cheers
JulieD

 

[Guest]

Thanks for your help. Problem solved.


David

Something like

=SUMPRODUCT(--(LEFT(M1:M1000,1)="C"),--(M1:M100="ABC"))

which counts the number in category ABC for cost centres starting with C.

If you want to sum another column, use

=SUMPRODUCT(--(LEFT(M1:M1000,1)="C"),--(M1:M100="ABC"), L1:L100)

--

HTH

RP
(remove nothere from the email address if mailing direct)