Hey NadoDude,
In case you were curious, the reason that barry houdini’s formula worked and mine did not is because of the following.
barry houdini’s Formula 1:
=SUMPRODUCT((Assignments!$G$2:$G$2633=A7)*(Assignments!$K$2:$K$2633<=M7)*((Assignments!$O$2:$O$2633>L7)+(Assignments!$O$2:$O$2633="")>0))
mgirvin’s Formula 2:
=SUMPRODUCT(--(Assignments!$G$2:$G$2633=A7),--(Assignments!$K$2:$K$2633<=M7),(Assignments!$O$2:$O$2633="")+(Assignments!$O$2:$O$2633>=L7))
With Formula 2 I assumed that the OR criteria, =blank and >=L7, were mutually exclusive. Meaning, that for any one record if there was a count of 1 for the blank there would never also be a count of 1 for the >=L7. But my assumption was incorrect because when there is a blank, the date in L7 is compared to a zero (blank cell) to get this:
BlankCell>=L7 = 0>=SerialNumberDate = TRUE
For the =blank criteria you would get:
BlankCell=”” = TRUE
So when you Boolean add you get:
TRUE + TRUE = 2
This adds 1 extra count of 1. If you had an array like this:
{2,0,1,0}
Your count would be 3 instead of 2.
barry houdini solved this be taking the array and creating the logical test:
{2,0,1,0}>0
Which yields:
{TRUE,FALSE,TRUE,FALSE}
And would lead to a count of 2.
Here is a video that takes the idea “Count with 3 AND criteria and the 3rd AND citeria contains an OR criteria” and shows how to use it to add, sum and do conditional formatting:
http://www.youtube.com/watch?v=zx8Csa0KP-E