Edit this code to add DATE filter... please?

[Sven62]

=COUNTIFS(EVENTS!H$4:H$2004,"Sunday",EVENTS!$J$4:$J$2004,"3")

What do I add to make this code also countif...

EVENTS!B$4:B$2004, "4/7/13 thru 4/20/13"?

 

[Marcelo Branco]

Try something like this

=COUNTIFS(EVENTS!H$4:H$2004,"Sunday",EVENTS!$J$4:$J$2004,"3",EVENTS!B$4:B$2004,">="&A1,EVENTS!B$4:B$2004,"<="&A2)

where A1 and A2 house respectively startDate and endDate

M.

 

[Sven62]

You sure about this? Doesn't seem to work.

 

[Marcelo Branco]

What you mean by Doesn't seem to wotk? Error? Wrong count?

I'm not seeing your data...Coud you post a data sample, say 10 to 15 rows, and expected results?


M.

 

[Sven62]

Oh man, it turned out to be whole different prob. Was bad sumproduct syntax on my part. apparently you can't have all those criteria and then G4:I2048. You have to separate G4:G2048 H4:H2048 I4:I2048 and use +. Anyway, I solved it this way..

=sumproduct(--(a1:a100>=date(2013,4,7)),--(a1:a100<=date(2013,4,20))...etc etc

Thank you very much for responding! Here is the final code:

=SUMPRODUCT(--(OVERTIME!$B$4:$B$2048>=DATE(2013,4,7)),--(OVERTIME!$B$4:$B$2048<=DATE(2013,4,20)),--(OVERTIME!$K$4:$K$2048="Sunday"),--(OVERTIME!$D$4:$D$2048=TRUE),--(OVERTIME!$C$4:$C$2048=1),OVERTIME!$G$4:$G$2048)
+SUMPRODUCT(--(OVERTIME!$B$4:$B$2048>=DATE(2013,4,7)),--(OVERTIME!$B$4:$B$2048<=DATE(2013,4,20)),--(OVERTIME!$K$4:$K$2048="Sunday"),--(OVERTIME!$D$4:$D$2048=TRUE),--(OVERTIME!$C$4:$C$2048=1),OVERTIME!$H$4:$H$2048)
+SUMPRODUCT(--(OVERTIME!$B$4:$B$2048>=DATE(2013,4,7)),--(OVERTIME!$B$4:$B$2048<=DATE(2013,4,20)),--(OVERTIME!$K$4:$K$2048="Sunday"),--(OVERTIME!$D$4:$D$2048=TRUE),--(OVERTIME!$C$4:$C$2048=1),OVERTIME!$I$4:$I$2048)

 

[Sven62]

Actually I just changed it to use your idea of putting the date range in two cells and referencing those cells instead of DATE(2013,4,7)

 

[Marcelo Branco]

Glad that you found out a solution.

Thanks for the feedback.

M.