array formula with multiple conditions

ok I have a named range called thisweekmon which returns me the date that was on monday of the current week

=IF(TEXT(NOW(),"ddd")="Mon",TODAY(),IF(TEXT(NOW(),"ddd")="Tue",TODAY()-1,IF(TEXT(NOW(),"ddd")="Wed",TODAY()-2,IF(TEXT(NOW(),"ddd")="Thu",TODAY()-3,IF(TEXT(NOW(),"ddd")="Fri",TODAY()-4,IF(TEXT(NOW(),"ddd")="Sat",TODAY()-5,TODAY()-6))))))

this seems to work fine.

Now I have a spreadsheet with column M containing Open or Closed, and column J containing a list of dates.

What I need is a formula which counts the number of dates in column J that are both Open in column M, and within the current week (hence my thisweekmon name)

I have got as far as getting it to count the number of dates where column M is Open, but I cant get it to only do this for dates that are => thisweekmon AND =<thisweekmon+6 (i.e. within the current week).

this is the array formula I have which does that

=IF(COUNT(IF($M$13:$M$10000="Open",$J$13:$J$10000,""))<>0,COUNT(IF($M$13:$M$10000="Open",$J$13:$J$10000,"")),"No items")

any ideas how to also get it to make the 2 additional checks to ensure its also within the current week (based on a mon-sun week) ? I tried adding an AND in there but it kept returning no results.

thanks

aha thanks

I managed it with

=SUMPRODUCT(--($M$13:$M$10000="Open")*($J$13:$J$10000>=thisweekmon)*($J$13:$J$10000<=thisweekmon+6))

seems to be working nicely