COUNTIF Unique Entries Only

I'm not certain this is possible but thought I would solicit some help.

Trying to complete a COUNTIFS with multiple criteria but include unique entries only. If anyone would opine it would be appreciated!!
=COUNTIFS('Data'!C6:C200,'Data'!D6:D200,"Office",'Data'!E6:E200,"**12")

The goal is to count all entries in column "C" that are unique if criteria in column "D" and column "E" are both met.

Please see attached file.

Many thanks!

Sorry losing,

But I can only come up with several formulas to accomplish what you want... I couldn't think of a way to do it in one magic formula...

Good luck!

Dennis

Array confirmed with Shift Ctrl Enter

=COUNT(1/FREQUENCY(IF(YEAR(A6:A11)=2012,IF(D6:D11="Office",MATCH(C6:C11,C6:C11,0))),ROW(D6:D11)-ROW(D6)+1))

Absolute genius! Thank you very much. This worked well!

If I may, would it be difficult to add the ,"", in the event it returned a zero to leave the cell empty vs. returning an actual zero? Trouble understanding the full logic so not certain where best to add that stipulation.

Hi losingmymind,

You can simply change the number format to not show the zeros... you can keep the formula as is...

Highlight your range (or cell) and right click on it... Format Cells, Custom, change the type to 0;;;

Let me know if this doesn't work...

Very true. Thanks for passing along!

Is it possible to add in two additional criteria? I attempted to add as seen below as <>Deal Done & <>Almost Done and it appears this is creating an issue with my YEAR criteria being overlooked/ignored. Instead the formula is just looking in the E column as the only excluding/including factor. Not certain why this would be the case? Any thoughts?


=COUNT(1/FREQUENCY(IF(YEAR(A6:A11)=2012,IF(E6:E11<>"Deal Done",IF(E6:E11<>"Almost Done",IF(D6:D11="Office",MATCH(C6:C11,C6:C11,0))))),ROW(D6:D11)-ROW(D6)+1))

Try

=COUNT(1/FREQUENCY(IF(YEAR(A6:A11)=2012,IF((D6:D11="Office")*(E6:E11<>"Almost Done")*(E6:E11<>"Deal Done"),MATCH(C6:C11,C6:C11,0))),ROW(D6:D11)-ROW(D6)+1))

Utter brilliance! Many many thanks.

So now I am amending all of my formulas because I was made aware of the concern about utilizing an asterick and the inherent errors that can occur. As such, I am not having difficulty keeping the cell blank if the sum / count returns a zero. Is there an clean way to add in the clause to leave the cell blank if the sum value is zero or must I duplicate the entire formula with a 0,"" at the end? Example below:

The forumla below is summing all values in column "R" if column "A"=2012, "D"=OFFICE and "E"=ALMOST DONE.

=SUM(IF(YEAR($A$10:$A$237)=2012,IF(($D$10:$D$237="OFFICE")*($E$10:$E$237="ALMOST DONE"),$R$10:$R$237)))

Any thoughts?

Keep your formula, but change your number format to not show 0's.

Highlight your range, right click, Format Cells, Custom... change the Type to something like... #,##0;;;

There are 4 types of formatting numbers... how you want to show your positives;negatives;zeros;text (separated by semicolons)

So #,##0 is how your positives would look like
the next semicolon forces the negatives to be blank
the next semicolon forces the zeros to be blank
and finally the last semicolon forces the text to be blank...

In your case, you want the zeros to be blank, so #,##0;;; would do the trick...

More confused?

Originally Posted by losingmymind

I was made aware of the concern about utilizing an asterick

Sounds like somebody condemning a formula they don't understand, the method used would be an issue with some functions, but none of those were used here.

Another way to hide 0 values without making changes to format.

=IFERROR(1/(1/ any formula),"")

No, not at all! Actually very much appreciate the "heads-up"! I am now attempting to clean up all of the other areas where this could also become an issue. Many thanks indeed.

This formula is awesome but it seems to be giving me such trouble when I want to edit it. For instance, I want to change the Year criteria to include just a particular Month, so I change the word Year to Month and change the criteria "=2012" to "=1112" but it does not appear to work. Additionally, I want to split the IF to ask it to SUM column "R" if "E"=ALMOST DONE and SUM column "T" if "E"=DONE. I cannot seem to get either of these two work properly. Any thoughts?

=SUM(IF(YEAR($A$10:$A$237)=2012,IF(($D$10:$D$237="OFFICE")*($E$10:$E$237="ALMOST DONE"),$R$10:$R$237)))