Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
Need total of column A (Consumer's Names) who have been assigned to specific
department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
=Sumproduct(--(A1:A100="name"),--(B1:B100="dept"),--(C1:C100d1),--(C1:C100<E1)) will give you the count of rows where
Col A has name, Col B has dept and Col has a date in month if D1=1/12008 and E1=1/31/2008 Adjust 100 to the end of your data set... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "ccreed61" wrote: Need total of column A (Consumer's Names) who have been assigned to specific department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
Correction
D1=12/31/2007 in mm/dd/yyyy format E1=2/1/2008 otherwise it will misss first and last day of Jan... The formula is for illustration. You can use the idea to build your formula... One way is to have another column with =Month(C1) to get 1 for Jan, 2 for Feb and then compare against that... then you need only once instance of C1:C100=1 instead of one < and one .... and so on... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Sheeloo" wrote: =Sumproduct(--(A1:A100="name"),--(B1:B100="dept"),--(C1:C100d1),--(C1:C100<E1)) will give you the count of rows where Col A has name, Col B has dept and Col has a date in month if D1=1/12008 and E1=1/31/2008 Adjust 100 to the end of your data set... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "ccreed61" wrote: Need total of column A (Consumer's Names) who have been assigned to specific department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
A pivot table is another great n fast "function"/way to handle this
Some easy steps: Assume your source table is in cols A to C, with col headers: Client, Dept, Date data from row2 down (Dates in C2 down are assumed real dates) Select any cell in the source table, click Data Pivot table Click NextNext. In step 3 of the wiz., click Layout, then: Drag n drop Dept & Date in ROW area, one below the other Drag n drop Client in DATA area (it'll appear as Count) Click OK Finish Hop over to the pivot sheet (just to the left) Right-click on Date Group & Show Detail Group Months OK Then just drag Date n drop it over "Total", to place all the grouped months into cols There you go, done in 15 seconds flat. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "ccreed61" wrote: Need total of column A (Consumer's Names) who have been assigned to specific department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
Hi
You can also use the following array formula SUM(IF((A2:A5="A")*(B2:B5="WE")*(C2:C5="Jan"),1)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "ccreed61" wrote in message ... Need total of column A (Consumer's Names) who have been assigned to specific department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
Thanks a bunch for your help. I knew I was making it harder than it needed to
be. Much appreciation...ccreed61 "Max" wrote: A pivot table is another great n fast "function"/way to handle this Some easy steps: Assume your source table is in cols A to C, with col headers: Client, Dept, Date data from row2 down (Dates in C2 down are assumed real dates) Select any cell in the source table, click Data Pivot table Click NextNext. In step 3 of the wiz., click Layout, then: Drag n drop Dept & Date in ROW area, one below the other Drag n drop Client in DATA area (it'll appear as Count) Click OK Finish Hop over to the pivot sheet (just to the left) Right-click on Date Group & Show Detail Group Months OK Then just drag Date n drop it over "Total", to place all the grouped months into cols There you go, done in 15 seconds flat. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "ccreed61" wrote: Need total of column A (Consumer's Names) who have been assigned to specific department (column B) in the month of January (Column C), February, etc. I have tried different formulas already posted but I'm not having any luck. I am using Excel 2003. Thanks for your help in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function to calculate total using multiple criteria
Welcome ..
-- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "ccreed61" wrote in message ... Thanks a bunch for your help. I knew I was making it harder than it needed to be. Much appreciation...ccreed61 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate total items meeting criteria in range with multiple shee | Excel Worksheet Functions | |||
calculate total number of items that meet 2 over multiple sheets | Excel Worksheet Functions | |||
Function to calculate total hours worked in one week. | Excel Worksheet Functions | |||
Average # in total group with multiple criteria? | Excel Worksheet Functions | |||
Counting total for multiple criteria | Excel Worksheet Functions |