Thanks. Glad we got that clarified! It means that we won't actually be able to simplify the formula as I previously stated, and will have to keep the one I posted earlier, the reason for which will hopefully become clear to you when I now try to explain the logic behind the formula.
I'll use the data in post #7 as it stands as a basis for explaining the formula.
Perhaps it's easiest if I start by explaining why the formula below (which would have worked but for what you just told me) doesn't</SPAN> work. I'll then explain the slight adjustment needed.
=SUMPRODUCT((MMULT(COUNTIFS($B$2:$B$21,$B$2:$B$21,$C$2:$C$21,{"Process Completed","Logo Returned","Logo Returned"},$D$2:$D$21,{"Completed","Completed","Cancelled"}),{1;1;1})>1)/COUNTIF($B$2:$B$21,$B$2:$B$21))
Taking this part:
COUNTIFS($B$2:$B$21,$B$2:$B$21,$C$2:$C$21,{"Process Completed","Logo Returned","Logo Returned"},$D$2:$D$21,{"Completed","Completed","Cancelled"})
In essence, for each of the 20 lines in the table, a 1x3 array is returned where the first element is a count corresponding to how many lines satisfy the following three criteria:
1) It has the same Proj Name</SPAN> as the line being evaluated
2) The Work Item</SPAN> status is Process Completed</SPAN>
3) The Proj Status</SPAN> is Completed</SPAN>
(Think of the criteria in curly brackets as "corresponding"; e.g. the elements in each position of each of these array constants are evaluated together, as an AND statement if you like.)
Likewise, the second element in that 1x3 array is a count corresponding to how many lines satisfy the three criteria:
1) It has the same Proj Name</SPAN> as the line being evaluated
2) The Work Item</SPAN> status is Logo Returned</SPAN>
3) The Proj Status is Completed</SPAN>
Finally, the third element in that 1x3 array is a count corresponding to how many lines satisfy the three criteria:
1) It has the same Proj Name</SPAN> as the line being evaluated
2) The Work Item</SPAN> status is Logo Returned</SPAN>
3) The Proj Status is Cancelled</SPAN>
To clarify what this looks like in Excel terms, that part of the formula, evaluated, will look like (I'll just give the first 12 rows' worth out of 20):
{1,1,1;0,0,0;1,1,1;1,0,1;1,1,1;0,0,0;1,0,1;1,1,1;1,1,1;1,1,1;0,0,0;1,1,0;...}
where, if you're not already aware, commas and semi-colons represent column and row separators respectively; so effectively we have returned a 20-row-by-3-column matrix of answers to our COUNTIFS query.
Here, the first series (top row) of 3 1s corresponds to the fact that, among the 20 lines, there is one line that is Project1/Process Completed/Completed</SPAN>, that there is one line that is Project1/Logo Returned/Completed</SPAN>, and also that there is one line that is Project1/Logo Returned/Cancelled</SPAN>.
Likewise, the second row of 3 0s corresponds to the fact that there are no lines that satisfy any of Project3/Process Completed/Completed</SPAN>, Project3/Logo Returned/Completed</SPAN> or Project3/Logo Returned/Cancelled</SPAN>, and so on.
I'll come to the rest of the technical part in a bit, but here's where the crux of the logic lies, since, if it was true, as I previously thought, that, for a given project, it was not</SPAN> possible to have both Logo Returned/Completed and Logo Returned/Cancelled</SPAN> in the table, then firstly we could eliminate those errors from your table, and secondly we would know that three 1s would be unachievable.
Based on that, we could safely make the assertion that, for any of those twenty "triplets", if two </SPAN>out of the three were 1s, then we would know that one of them must have come from Process Completed/Completed</SPAN> and that one of them must have come from either </SPAN>Logo Returned/Completed </SPAN>or Logo Returned/Cancelled, </SPAN>as required.
However, since you now say that it is </SPAN>actually possible to have the two Logo Returned</SPAN> statuses in there simultaneously, knowing that two of the three criteria were satisfied would not guarantee success, since these two could equally have come from 1s for both Logo Returned/Completed </SPAN>and Logo Returned/Cancelled</SPAN>.
Based on this, we need the slightly longer formula which you tested:
=SUMPRODUCT((MMULT(COUNTIFS($B$2:$B$21,$B$2:$B$21,$C$2:$C$21,{"Process Completed","Process Completed","Logo Returned","Logo Returned"},$D$2:$D$21,{"Completed","Completed","Completed","Cancelled"}),{1;1;1;1})>2)/COUNTIF($B$2:$B$21,$B$2:$B$21))
You can probably see that the key difference is that each line is now being evaluated against four criteria, not three as previously. You might also notice that the extra criterion is simply a duplication of the first one, i.e. that we now have Process Completed</SPAN>, and it's "counterpart" in the other array constant – Completed - </SPAN>, appearing twice.
This might seem odd, but the reason it works is that, if we now define our condition for a line to be "counted" as there being 3 out of 4 of these criteria satisfied, we can easily see that, if this is the case, the only possibilities are that this 3 was made up from either:
2 x Process Completed/Completed </SPAN>and 1 x Logo Returned/Completed</SPAN>
2 x Process Completed/Completed </SPAN>and 1 x Logo Returned/Cancelled</SPAN>
Either way, both mean that our required conditions get satisfied. Unlike previously, there is no possibility of making the count of 3 from e.g. solely Logo Returned </SPAN>statuses.
The artificial repetition of one of the conditions may seem odd, but it also seems to be justified!
All we need now is a way of determining how many of these "quadruples" contain at least three 1s, which is done via MMULT. I haven't got the time or space to explain matrix multiplication in this post, suffice to say that multiplying a 20-row-by-4-column matrix and a 4-row-by-1-column unit matrix ({1;1;1;1}), in that order, will produce a 20-row-by-1-column matrix, the entries of which will be our required count of 1s per line, i.e. using your original table:
{4;0;4;3;4;0;3;4;4;4;0;3;4;4;4;3;0;0;0;0}
This is then tested against the criterion of being 3 or greater, to give (I've replaced TRUE and FALSE with T and F):
{T;F;T;T;T;F;T;T;T;T;F;T;T;T;T;T;F;F;F;F}
The total of 13 TRUEs is evidently not our final answer, though: since we have evaluated the conditions over all 20 lines (which was necessary), there will be a TRUE corresponding to every instance that a given Project satisfied the conditions, equivalent to the 5 instances of Project1</SPAN>, the 2 of Project2</SPAN>, the 4 of Project5</SPAN> and the 2 of Project6.</SPAN>
A final, simple operation to return a unique count is then performed: divide this array by the number of occurrences for each project (COUNTIF($B$2:$B$21,$B$2:$B$21)) and sum the result, i.e.:
SUMPRODUCT({T;F;T;T;T;F;T;T;T;T;F;T;T;T;T;T;F;F;F;F}/{5;2;4;2;5;3;2;5;4;5;3;2;4;5;4;2;2;3;1;1})
which becomes:
SUMPRODUCT({0.2;0;0.25;0.5;0.2;0;0.5;0.2;0.25;0.2;0;0.5;0.25;0.2;0.25;0.5;0;0;0;0})
which is 4, as required.
Hope that helps.
Regards</SPAN>
></o
