COUNTIFS with a unique identier

[tomthumb2011]

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.

[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

[tomthumb2011]

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

[tomthumb2011]

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

Would appreciate any help.

[Richard Buttrey]

Hi,

Often with cases like this the answer is to create a helper column, in this case a couple of helper columns.

The count formula is now in H2 and is an array formula, i.e. must be entered with Ctrl-Shift-Enter.

Regards