function to calculate total using multiple criteria

[ccreed61]

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.

 

[Sheeloo]

=Sumproduct(--(A1:A100="name"),--(B1:B100="dept"),--(C1:C100>d1),--(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...

 

[Sheeloo]

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...

 

[Max]

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 Next>Next. 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

 

[Ashish Mathur]

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]

Thanks a bunch for your help. I knew I was making it harder than it needed to
be. Much appreciation...ccreed61

 

[Max]

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik