Unique values with multiple criteria

[mlouise]

I have a dilemma. I have a spreadsheet I put together for my research. I have each subject assigned to a unique ID # (in column A). In one worksheet I have complications and each row is a complication that occurred, so there may be multiple rows for any given subject ID #.

I had created a worksheet to show results and have been using the following formula to count unique subjects that had a complication based on criteria in two other columns.

=COUNT(1/FREQUENCY(IF(('Complications'!E2:E135=1)*('Complications'!H2:H135=1),MATCH('Complications'!A2:A135,'Complications'!A2:A135,0)),ROW('Complications'!A2:A135)-MIN(ROW('Complications'!A2:A135)+1)))

The problem I have run into is that when I sort my complications worksheet the above formula produces a different answer each time.

I would appreciate any insight you might have.

Thanks!

 

[T. Valko]

I have a dilemma. I have a spreadsheet I put together for my research. I have each subject assigned to a unique ID # (in column A). In one worksheet I have complications and each row is a complication that occurred, so there may be multiple rows for any given subject ID #.

I had created a worksheet to show results and have been using the following formula to count unique subjects that had a complication based on criteria in two other columns.

=COUNT(1/FREQUENCY(IF(('Complications'!E2:E135=1)*('Complications'!H2:H135=1),MATCH('Complications'!A2:A135,'Complications'!A2:A135,0)),ROW('Complications'!A2:A135)-MIN(ROW('Complications'!A2:A135)+1)))

The problem I have run into is that when I sort my complications worksheet the above formula produces a different answer each time.

I would appreciate any insight you might have.

Thanks!

Unable to reproduce your problem.

This formula might be slightly more efficient.

Still array entered.

=SUM(IF(FREQUENCY(IF(Complications!E2:E135=1,IF(Complications!H2:H135=1,MATCH(Complications!A2:A135,Complications!A2:A135,0))),ROW(Complications!A2:A135)-ROW(Complications!A2)+1),1))

 

[mikerickson]

Have you looked at DCOUNT?

 

[barry houdini]

Sorting rows 2 to 135 (or a subset of that range) on Complications sheet shouldn't cause the result of that formula to change.

Are you sorting more rows than that?

Are you including all the columns in the sort so that all data stays within the same row?

Do you have formulas in the cells your are sorting or just data?

 

[mlouise]

Thank you for all the responses. I figured out that it was another column causing the problem. The formula in that column is:

=VLOOKUP('Complications'!$A2,Overall!$A$2:$T$200,20,FALSE)

And for some reason when I sort, the $A2 does not update to the new row that it is located in. It maintains the $A2 even though it may be in row 57 after I sort, and I need it to update to $A57. I have another column with a similar formula that updates appropriately. Any thoughts?

Again, thanks for the input!

 

[T. Valko]

Thank you for all the responses. I figured out that it was another column causing the problem. The formula in that column is:

=VLOOKUP('Complications'!$A2,Overall!$A$2:$T$200,20,FALSE)

And for some reason when I sort, the $A2 does not update to the new row that it is located in. It maintains the $A2 even though it may be in row 57 after I sort, and I need it to update to $A57. I have another column with a similar formula that updates appropriately. Any thoughts?

Again, thanks for the input!

Well, at this point I don't know what else to suggest. I'd need to see the file to figure out what it's doing and how it's affecting the count formula.