counting unique values with multiple criteria

Hi There,

I'm a relative novice at Excel and I'm having trouble working out a formula.

I'm trying to count the unique numbers in ROW G (Sectors) if certain criteria are met in other cells. For example,
IF (1_hypothesis = 9) AND (1_for/against = D OR 1_for/against = CF) AND (1_thick/thin = Thick) AND (2_hypothesis = 9) AND (2_for/against = D OR 2_for/against = CF) AND (2_thick/thin = Thick), I want it to count the number of unique numbers in Sectors (Row G).

I've tried SUM (with FREQUENCY, MATCH) and I get mostly there, but it stops working when I add more criteria.

If anyone could provide me with the formula, that would be great.

Thanks so much!!

Screen Shot 2016-11-30 at 9.06.23 AM.png

Try this formula

Formula:

Please Login or Register  to view this content.

Hi,

Thank you for your help! It mostly works. The only issue is that it's doing a count of all things that fit the criteria rather than simply counting UNIQUE numbers. In the example above, there number returned should be 2, but I'm getting 3 (the total number of cells that meet the criteria). Is there a way to have it count the UNIQUE values?

Thanks again for your help!

Hi
Try this formula

Formula:

Please Login or Register  to view this content.

See the file

Now I get #N/A

Is this error in the file I attached?

Please, can you send me the formula you are using, or attach a workbook?

I've attached my workbook. Thanks!

See the file and send me feedback for my formula

I think I need to clarify what I'm looking for.

I'm looking for a formula that will count the UNIQUE values in Column G with the following criteria:

[IF (1_hypothesis = 9) AND (1_for/against = D OR 1_for/against = CF) AND (1_thick/thin = Thick)]

OR

[(2_hypothesis = 9) AND (2_for/against = D OR 2_for/against = CF) AND (2_thick/thin = Thick)]

Thanks!

Hi
Sorry for my mistake. Regular formula is

Formula:

Please Login or Register  to view this content.

which gives you the value 2 (Sectors 1 and 2).
See the file

Almost there! The result is correct when column G is as is. But when I test it making all the "sectors" either 2 or 1, the result is not consistent. See worksheet Test1 and Test 2.

Hi,
Can you reattach the file. When I open it, it appears to be corrupt.

Thanks!

Ok.

Here is the file

Thank you! It's a bit laborious, but it does work.

Just to add another wrinkle, what if I add another criteria? I'd like to add a fourth criteria to each part, but when I try, it doesn't work. Any suggestions? See attachment -- the orange are what the correct pathways should be.

Try this

Formula:

Please Login or Register  to view this content.

All the answers are 2 and the unique number is 1 (should be 2).

Hi
See the formula in M2

Formula:

Please Login or Register  to view this content.

It's include ROW(A1:A100) and ROW(A1:A101) to count unique numbers graters than 0 and less than 100 for distinct Sectors.

See test2 in the file

Originally Posted by FlameRetired

In post #15 orange pathways ... isn't A6:D6 also a correct pathway and therefore I6 should be included?

No, because D6=5, not 1.