Sum formula for criteria between two dates on separate sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 sheets A and B with dates and case managers on them. I need a formula on a 3rd sheet C to count all the dates in between/and to include 1/1/04 and 12/31/04 on A and B, but only if they have the case managers judy, maralee, theresa, chuck, or cheryl. All I need is a total nothing more.
 
Hi
if column A stores the date and column B the case manager name on both
sheets you may try the following "monster":
=SUMPRODUCT(('sheetA'!A1:A1000>=DATE(2004,1,1))*('sheetA'!A1:A1000<=DAT
E(2004,4,31))*(('sheetA'!B1:B1000="judy")+('sheetA'!B1:B1000="maralee")
+('sheetA'!B1:B1000="theresa")+('sheetA'!B1:B1000="chuck")+('sheetA'!B1
:B1000="cheryl")>0)) +
SUMPRODUCT(('sheetB'!A1:A1000>=DATE(2004,1,1))*('sheetB'!A1:A1000<=DATE
(2004,4,31))*(('sheetB'!B1:B1000="judy")+('sheetB'!B1:B1000="maralee")+
('sheetB'!A1:B1000="theresa")+('sheetB'!B1:B1000="chuck")+('sheetB'!B1:
B1000="cheryl")>0))
 
Back
Top