#### Sven62

##### Active Member
Code:
``=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"?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

Last edited:

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.

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:

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)``````

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)

Glad that you found out a solution.

Thanks for the feedback.

M.

Replies
3
Views
94
Replies
2
Views
317
Replies
1
Views
165
Replies
4
Views
240
Replies
3
Views
223

1,196,498
Messages
6,015,557
Members
441,901
Latest member
joshtfin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back