Countifs problem with dates

[Mark_E]

I am trying to get a count of dates in column I that are equal to or less than the date in column J for each worker whose name is in a rotating series in column C.

SO far, I have

=COUNTIFS('April 2016 CARL Assigned'!$C:$C,"'April 2016 CARL Assigned'!$C2",'April 2016 CARL Assigned'!$J:$J,"<='April 2016 CARL Assigned'!$I:$I")

But this does not work.

Breaking it down,

=COUNTIFS('April 2016 CARL Assigned'!$B:$B,"'April 2016 CARL Assigned'!$B2")

return the correct value, so it is the comparison of I:I to J:J that the problem.

The data looks like this, but in a 2500 row table:

<colgroup><col><col></colgroup><tbody>
</tbody>

J	I
8/1/16	8/1/16
7/20/16	7/18/16
8/7/16	8/8/16
8/5/16	8/9/16
8/6/16	8/4/16
7/27/16	7/27/16
8/5/16	8/1/16
8/5/16	8/4/16

<tbody>
</tbody>

Any help is much appreciated.

 

[Mark_E]

Sorry, all $B:$B should be $C:$C, cannot edit post.

 

[Jonmo1]

Countifs won't be alble to do that..

Try
=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C:$C='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J:$J<='April 2016 CARL Assigned'!$I:$I))


However, I HIGHLY recommend NOT using entire column references like C:C in this formula.
Restrict it to your actual used area, like

=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C$1:$C$1000='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J$1:$J$1000<='April 2016 CARL Assigned'!$I$1:$I$1000))

 

[Mark_E]

Countifs won't be able to do that..

=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C$1:$C$1000='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J$1:$J$1000<='April 2016 CARL Assigned'!$I$1:$I$1000))

This produces the same result as:

=COUNTIFS('April 2016 CARL Assigned'!$C:$C,'April 2016 CARL Assigned'!$C2)

It delivers a count of the number of occurrences of the worker's name, but it is not giving me a count of the number of dates in J that are less than or equal to the date in I.

 

[Jonmo1]

This produces the same result as:

=COUNTIFS('April 2016 CARL Assigned'!$C:$C,'April 2016 CARL Assigned'!$C2)

If it is, it's purely coincidental, not logically correct.

This is only counting rows where column C are equal to C2
=COUNTIFS('April 2016 CARL Assigned'!$C:$C,'April 2016 CARL Assigned'!$C2)


This is counting rows where column C are equal to C2 AND the value in column J is less than or equal to the value in column I
=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C:$C='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J:$J<='April 2016 CARL Assigned'!$I:$I))

 

[Mark_E]

If it is, it's purely coincidental, not logically correct.

This is counting rows where column C are equal to C2 AND the value in column J is less than or equal to the value in column I
=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C:$C='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J:$J<='April 2016 CARL Assigned'!$I:$I))

It must be counting blank cells in I & J as being equal. Is there a way to do a SUMPRODUCT that ignores blanks?

 

[Jonmo1]

Try
=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C:$C='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J:$J<='April 2016 CARL Assigned'!$I:$I),--('April 2016 CARL Assigned'!$J:$J<>""))


Again, I HIGHLY recommend NOT using entire column references like $C:$C in this formula.
Restrict it to your actual used area, like $C$1:$C$1000

 

[Mark_E]

Try
=SUMPRRODUCT(--('April 2016 CARL Assigned'!$C:$C='April 2016 CARL Assigned'!$C2),--('April 2016 CARL Assigned'!$J:$J<='April 2016 CARL Assigned'!$I:$I),--('April 2016 CARL Assigned'!$J:$J<>""))


Again, I HIGHLY recommend NOT using entire column references like $C:$C in this formula.
Restrict it to your actual used area, like $C$1:$C$1000

That worked great, I had already made the changes to the range, but copied the old formula :/

Thanks for the help!

 

[Jonmo1]

You're welcome.