Thread: COUNTIFS with a unique identier

All,
Long time listener, first time poster. I was wondering if anyone could help me with the below predicament.

Excel 2010

I have a countifs function that is working perfectly.

1

=COUNTIFS('Risk & Control Repository'!H9:H9624, O7, 'Risk & Control Repository'!$W$9:$W$9624,CONCATENATE($D$13,E$16))

However, I want to add one more criteria to it.

2

=COUNTIFS('Risk & Control Repository'!H9:H9624, O7, 'Risk & Control Repository'!$W$9:$W$9624,CONCATENATE($D$13,E$16), THIS COLUMN, ISUNIQUE)

i.e.
1) will return duplicated rows
2) will not as THIS COLUMN is a unique identifier.

I want to return all unique rows based on the unique identifer that meet the other criteria.

Any and all help would be very much appreciated.

Originally Posted by Richard Buttrey

Hi,

Assuming you only have Excel 2003 and not 2007/2010 (where you could have used the new COUNTIFS() function), you'll need to use instead either an array formula or SUMPRODUCT()

Upload your workbook so that we can see your request in context.

Regards

Hi Richard,
Thanks for the quick response. I'm on Excel 2010 and as above am using COUNTIFS. Is their another COUNTIFS that would support the above?

I can use sumproduct to count the number of unique values but I still need to set the other criteria as stipulated. Unfortunately the workbook is work-sensitive so I can't upload. Let me know if you can't take a stab based on the above and I'll create something simular for upload.

Paul

Guys,
I've attached a simplified example of what I'm trying to do.

Would appreciate any help.