multiple criteria adding

[Cablelane]

Hello

I have 20 employees; not all of them work every day. They all keep track of their scheduled appointments, cancelled appts, and no-shows on their individual worksheet, on a daily basis, in a shared workbook. The cancellation/no-show rate is calculated based on these numbers. How can I calculate department CA/NS rate, which I would like to be able to show per month? All help appreciated.

Thanks!

 

[Jonmo1]

Welcome to the board.

well without knowing specifics, ranges/criteria, here is a general sample of how to do a multi criteria sum

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),RangeToSum)

example, this will sum Column C if Column A = "Yes" and Column B = "Jon"

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100="Jon"),C1:C100)

IMPORTANT NOTES
Ranges cannot be entire column refs like A:A, must use Row #s like A1:A100 - except in XL2007
Ranges must be same size
YES, the -- needs to be there.

Hope that helps..

 

[Cablelane]

Thanks for your quick and helpful reply. I think SUMPRODUCT will work. One refinement I need is to have the criteria include checking for date, on each of the 20 worksheets. Sorry for not posting example first.

Example:
Date Scheduled Cancel No-Show Rate
2/23/09 12 1 1 Thanks!

 

[Cablelane]

Sorry to be a bother. Anyone have any ideas on how I can get this data from the many worksheets onto one?

Thanks in advance for your assistance.