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.

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.

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.

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.

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.

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.

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