SUMPRODUCT - Count where date difference

[djmixer135]

Hi there,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Submitted[/TD]
[TD]Assessed[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]01/01/2019[/TD]
[TD]05/01/2019[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]01/01/2019[/TD]
[TD]31/01/2019[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]01/01/2019[/TD]
[TD]20/02/2019[/TD]
[/TR]
</tbody>[/TABLE]

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.