Countifs distinct values

**mpeter2612** · 01-04-2013, 01:08 PM

Hello.

what I'm trying to accomplish is to count specific values in two columns in a worksheet (say, column A, and column B).

= countifs(column A, "Complete", column B, "Joe")

The above formula works for counting values in A, and B. Where my problem lies, is I want to only count values from the above formula based off distinct values in another column (say, column C). So, I want to count A = complete, and B = Joe, but if the value (numeric) is repeated in column C, then only return 1 for the count of "Complete, "Joe".

I hope the above makes sence. Thanks in advance for your help.

**icestationzbra** · 01-04-2013, 01:26 PM

ARRAY formula:

Please Login or Register  to view this content.

or this one to include non-numeric values in column C:

Please Login or Register  to view this content.

or, this non-array formula, which handles both numeric and non-numeric values in column C:

Please Login or Register  to view this content.

**mpeter2612** · 01-04-2013, 03:20 PM

Thanks for your help.

I tried using all three formula's you provided and the array formula's are giving me a if ( typeof(autoid) === 'undefinded') error of some sort. I'm using named ranges for the source for this formula. Not sure if that's the problem. For the array I am doing CTRL + SHIFT + ENTER.

The non array formula returns the value of 1, but is not counting the other distinct values of column C.

Below is a sample of the data that I'm refering to. I'm wanting to count all "Complete"/"Joe", but only count them once if column C is repeated. So the formula would return the value of 3 for the below data, instead of 4.

A B C
Complete Joe 1
Complete Joe 2
Complete Joe 2
Complete Joe 3

Code I entered
Returns error

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Returns 1, when should be 3

Please Login or Register  to view this content.

Thanks again for your help!

**Moo the Dog** · 01-04-2013, 04:14 PM

@mpeter:

Are all three named ranges the same size? If they are not the same size, or if they include an entire column you will likely get an error.
I tested all three formulas and they work just fine based on your example data.

- Moo

**mpeter2612** · 01-04-2013, 04:25 PM

@Moo the Dog

Yes they are all the same size within thier respected columns, and they aren't for an entire column, although the named ranges aren't set up to be dynamic, so there will be many blank cells within the range. I'll come up with a sample sheet and upload it. Thank you all!