Is this possible?

[dhx10000]

Is there any way to count the value of one column based on if another column is unique? For example, I have a column called 'Issue ID' that is a numeric value that is sometimes repeated because each record that is entered in this application can be modified. So the database keeps a tally of what has been modified on the record by duplicating the record every time it is modified. The problem is there is another field called 'Status', with either an 'Open' or 'Closed' value. I need to count all 'Open' statuses but make sure I count it for only one unique 'Issue ID'.

Example:
Issue ID---------Status
3 Open
3 Open
3 Open
4 Closed
5 Closed
5 Open
-----------------------------

So the correct value of 'Open' statuses would be 2.

Hope that makes sense,

Sal

 

[Aladin Akyurek]

Is there any way to count the value of one column based on if another column is unique? For example, I have a column called 'Issue ID' that is a numeric value that is sometimes repeated because each record that is entered in this application can be modified. So the database keeps a tally of what has been modified on the record by duplicating the record every time it is modified. The problem is there is another field called 'Status', with either an 'Open' or 'Closed' value. I need to count all 'Open' statuses but make sure I count it for only one unique 'Issue ID'.

Example:
Issue ID---------Status
3 Open
3 Open
3 Open
4 Closed
5 Closed
5 Open
-----------------------------

So the correct value of 'Open' statuses would be 2.

Hope that makes sense,

Sal

If you download and install the latest version of the free morefunc.xll add-in...

Book3
	A	B	C	D	E
1
2	Issue ID	Status		# of Distinct Open ID's	2
3	3	Open
4	3	Open
5	3	Open
6	4	Closed
7	5	Closed
8	5	Open
Sheet1

E2:

=COUNTDIFF(IF(B3:B8="Open",$A$3:$A$8,0),FALSE,0)

which must be confirmed with control+shift+enter, not just with enter.

 

[dhx10000]

tHANK YOU this was a life saver!