Countif/Sumproduct, cell date falls between two dates and criteria in cell is marked as completed

[slayer1957]

Hi,

I have specified two A1 and A2 with start date and finish date of month, basically A1=01/01/2021 and A2=31/01/2021.

Then on Sheet 2 i have a list of projects completion dates in range J2:J200
On sheet 2 E2:E200 column there is marked "Completed"

On any cell i need a formula to check the if the date in J2:J200 falls between the dates specified in A1 and A2 and if the cell is marked as completed then it must count as 1. In other words if multiple projects date was listed as the 19/01/2021, 23/01/2021/31/01/2021 then marked as completed on all of them it must show in the cell where the formula is as a count of 3 completed.

I have this formula thus far for the dates, but need to add criteria of completed but i cant get it right. This count the cells if project dates fall between and show results when dates are all in range but i need it to show only when it is also marked as completed

=SUMPRODUCT(('Sheet2'!$J$2:$J$200>=A$1)*('Sheet2'!$J$2:$J$200<=A$2))

 

[FormR]

Hi, you'd generally use COUNTIFS() for conditional counting, for example.

=COUNTIFS('Sheet2'!$J$2:$J$200,">="&A$1,'Sheet2'!$J$2:$J$200,"<="&A$2,'Sheet2'!$E$2:$E$200,"Completed")

 

[slayer1957]

Thanks, so simple just did not see the =countifs function