Counting instances with multiple criteria

[crygon]

Hello,
I have a database that I'd like to count the number of times a particular phrase comes up for a particular person. These must be unique values. I'll post a sample to show what I mean.

In the example, I'd like a value to be posted on Sheet 1 where the question mark is. It would find a total of unique failures from the test column in sheet 2. The desired outcome I want is listed on sheet 1. Basically, I want a formula that would count each failure number once for a given employee.

Thanks in advance.

[Ron Coderre]

This isn't pretty, but this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of just ENTER) seems to do what you want...

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

[crygon]

Thanks for the quick response. I entered that formula for D3 and it did not calculate when I pressed ctrl, shift, enter. I am on a mac and also attempted cmd, shift, enter. Should I be doing something different? Also, if the data changes in sheet 2, do I have to press ctrl, shift, enter again?

I apologize, I have never used array formulas before.

EDIT: Cancel that, I wasn't in the cell when I tried. It works like a charm. Now just have to integrate it into my data. Thanks a million!

[FDibbins]

so for employee 1, failure: 123 is different to failure: 456, right?

[Ron Coderre]

Thanks for the quick response. I entered that formula for D3 and it did not calculate when I pressed ctrl, shift, enter. I am on a mac and also attempted cmd, shift, enter. Should I be doing something different? Also, if the data changes in sheet 2, do I have to press ctrl, shift, enter again?

I apologize, I have never used array formulas before.

EDIT: Cancel that, I wasn't in the cell when I tried. It works like a charm. Now just have to integrate it into my data. Thanks a million!

You're very welcome....I'm glad I could help.

[crygon]

Can't seem to get it to work. In our example Sheet2 does not have quotes. My actual spreadsheet's sheet name is cvs - data. Excel wants quotes so in fomula it ends up being 'cvs - data'. Is there an issue with this? Is there anywhere that I can get more detail on why/how the formula works?

[crygon]

Okay. Think I might be getting into another problem. My "test" column is more than just "failure" and a number. It's "failure" a number, followed by some notes. These notes may be different for each "failure-123". I want "failure-123 Hello" and "failure-123 Goodbye" to only count as one.

[crygon]

so for employee 1, failure: 123 is different to failure: 456, right?

Yes. I also found that I need a little more. In that column there will be a "failure: 123 Hey buddy" and "failure: 123 That's not good". I would want these to only count as one.

[FDibbins]

would the extra text be in its own column, or part of the 123 etc?

[crygon]

would the extra text be in its own column, or part of the 123 etc?

Unfortunately, in the same column. The data is extracted from a FileMaker database.