Worksheet Count Function Help

Hi,

As far as I understand you the furmula you need might bethe following:

From the example you should be able to figure out how to modify it to suit your needs.

Best,

J.Ty.

Hi,

It's unclear to me from your description what your desired results are for that table.

Regards

Add the following two functions coded in VBA to your workbook, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function


Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Now invoke...

Control+shift+enter, not just enter:

@J.Ty.
I believe the count should be 1.

XOR LX,

The desired results are to provide a single value for how many projects have been completely manufactured. As another way to state it, the following conditions must be true in order for it to be a value of 1.
Project1 = Process Complete under Work Item is true and Completed under Proj Status is true and Logo Returned under Work Item is true and Completed under Proj Status is true and Logo Returned under Work Item and Canceled under Proj Status is true.

A table may be better to understand. All the following conditions must be true in order for the Project to count as a value of 1. There can be a situation where Logo Returned is Canceled but I still want to be able to count the Project1 as a value of 1. The data that is imported from the other software can be any number of rows long. I have been trying to use COUNTIFS to do this but no success yet. I may try using an If-Then statement with the COUNTIFS function to see if I can get something. I hope this better explains what I am trying to accomplish. Let me know if you need further explanation.

Thanks. Excellent explanation.

Would it also be possible to have a table of perhaps twenty or so random lines with your expected result so that I can verify any solutions? Obviously it might be an idea to include lines which feature none of the required statuses, if possible.

Regards

XOR LX,

The number of projects considered to be installed would be three (3) in this example (see table below). Tried to highlight the text but not successful. See Proj Count column. It is just showing what to match and count. If you look at 1, 2 and 3 in the Proj Count column, it will match the criteria. My criteria are the following:

Work Item = Process Completed AND Proj Status = Completed for the Process Completed Work Item
Work Item = Logo Returned AND Project Status = Completed or Cancelled for Logo Returned Item

ALL of the above must be true for the project to count as 1.
Again, I hope this table is a better explanation than before.

Hi again,

Would it be possible for you to clarify the conditions? It's just that your two statements don't seem consistent, unless I'm misunderstanding:

94mustang said:

All the following conditions must be true in order for the Project to count as a value of 1.

Proj Name	Work Item	Proj Status
Project1	Process Complete	Completed
Project1	Logo Returned	Completed
Project1	Logo Returned	Canceled

<TBODY>
</TBODY>

Click to expand...

94mustang said:

My criteria are the following:

Work Item = Process Completed AND Proj Status = Completed for the Process Completed Work Item
Work Item = Logo Returned AND Project Status = Completed or Cancelled for Logo Returned Item

ALL of the above must be true for the project to count as 1.

Click to expand...

Your latest post seems to suggest that you only need two lines in total to be satisfied for a given Project, i.e. with Work Item = Logo Returned, the Project Status can be either Completed or Cancelled.

Whereas the previous post seemed to suggest that you in fact must have three lines in total satisfied, and that, with Work Item = Logo Returned, there must be both a line with Project Status Completed and a separate line with Project Status Cancelled.

Can you please clarify?

Regards

XOR LX,

Your first assessment of the criteria is correct. The first line of course is Work Item = Process Complete with Project Status Completed. The second one would be with Work Item = Logo Returned with Proj Status Completed or Cancelled. It would not be both. Only one or the other. Thanks for continuing to ask questions because this one I think is not so simple. Let me know if I have better clarity in the explanation or made it less confusing. Thanks again.

Thanks.

Can you test this formula, based on a range of B2:D21 (obviously extend if necessary), and get back to me?

=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))

Regards