SUMPRODUCT - Count where date difference

[djmixer135]

Hi there,

Name	Submitted	Assessed
Bob	01/01/2019	05/01/2019
John	01/01/2019	31/01/2019
Fred	01/01/2019	20/02/2019

<tbody>
</tbody>

I'm looking to use SUMPRODUCT function to count the number of records in the table above where the date difference between column B and C is less than 10 days. In the example above, the SUMPRODUCT formula should return a value of 1.

Please advise as to what the formula would look like for this.

Many thanks!

 

[gaz_chops]

Try

=SUMPRODUCT((C2:C4<(B2:B4+10))*1)

 

[djmixer135]

Thanks! That's really helpful. As an extension to this question, please can you explain why this won't work?

SUMPRODUCT(--('Assessments'!$C$2:$C$10000<('Assessments'!$B$2:$B$10000+10), --('Assessments'!$A$2:$A$10000="Bob"))

This should only count records where name = Bob

 

[gaz_chops]

Missing a )

=SUMPRODUCT(--('Assessments'!$C$2:$C$10000<('Assessments'!$B$2:$B$10000+10)), --('Assessments'!$A$2:$A$10000="Bob"))

 

[djmixer135]

OF COURSE!!!

Thanks so much

 

[gaz_chops]

You're welcome.