Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Two variables
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. |
#2
|
|||
|
|||
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 "Blackcat" wrote in message ... 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. |
#3
|
|||
|
|||
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) "Blackcat" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote in message ... 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) "Blackcat" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote in message ... 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 "Blackcat" wrote in message ... 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. |
#6
|
|||
|
|||
Hi Bob
thanks for this ... Cheers JulieD "Bob Phillips" wrote in message ... 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" wrote in message ... 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 "Blackcat" wrote in message ... 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. |
#7
|
|||
|
|||
Thanks for your help. Problem solved.
David "JulieD" wrote: 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 "Blackcat" wrote in message ... 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. |
#8
|
|||
|
|||
Thanks for your help. Problem solved.
David "Bob Phillips" wrote: 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) "Blackcat" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use variables for a chart | Charts and Charting in Excel |