Count unique occurrences with sumproduct

Hi,

I have excel 2010 and one function which i could not figure out how to combine with my idea of adding unique occurrence check per row.

$B5:$B404 (or $B:$B) - is the ratio that may occur duplicates.

B column
12-2252533
12-9542546
12-9542546
12-8744031

in the U6 cell i currently have =SUMPRODUCT(--($J:$J="");--($C:$C="EXTRA")) - i want this function to remain the same but to combine with the occurrence check function.

above will output sum of 4 to U6, but this should be 3 because i only want one occurrence to count.

I searched the forum talking about that i should use something like (1/COUNTIF($B:$B;$B:$B)), but this is not correct coding itself.



Any help is much appreciated! :)

It's not real clear what you're wanting to do.

Here's my best guess...

Count unique entries in column B if column J is blank/empty and column C contains "Extra".

Array entered**:

=SUM(IF(FREQUENCY(IF(J2:J10="",IF(C2:C10="EXTRA",MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You should avoid using entire columns as range references in array formulas.

If possible with conditional formatting,

can it automatically make foreground color in active cell to standard yellow if that duplicate occurs ?

I mean yellow from cell A to cell N - which i have data in.


Thanks again.

Yeah, the folks at Microsoft sure didn't make it easy to work with unique entries!

Thanks for the feedback!

If I understand, Excel 2010 conditional formatting has a Duplicates option.

I don't have Excel 2010 but here are the steps to take in Excel 2007 which should be about the same.

Select the range of cells in question

Goto the Home tab>Styles>Conditional Formatting>Highlight Cell Rules>Duplicate Values

I see this impossible with conditional formatting.

I only want to hightlight if duplicate occurs in row B5:B404 and highlight foreground from only active row, from cell A to cell N


Cheers.

OK, for that you would need an event macro.

I'm not much of a programmer so someone else would need to help you with that.

You're welcome!

Good luck!

Sorry, I don't understand.

Good deal. Thanks for the feedback!

In addition to your first provided function now i would like to count rows where EXTRA and CATEGORY met togheter (row C and row D).


I made a change to the function but it contains a error inside, i dont know how to fix that

Modified function with error:
=SUM(IF(FREQUENCY(IF(J5:J1004="";IF(AND(C5:C1004="EXTRA");D5:D1004="CATEGORY2";MATCH(B5:B1004;B5:B1004;0))));ROW(B5:B1004)-ROW(B5)+1);1))


Unmodified function:
=SUM(IF(FREQUENCY(IF(J5:J1004="";IF(C5:C1004="EXTRA";MATCH(B5:B1004;B5:B1004;0)));ROW(B5:B1004)-ROW(B5)+1);1))


BIG thanks!!

Try it like this:

=SUM(IF(FREQUENCY(IF(J5:J1004="";IF(C5:C1004="EXTRA";IF(D5:D1004="CATEGORY2";MATCH(B5:B1004;B5:B1004;0))));ROW(B5:B1004)-ROW(B5)+1);1))

Still array entered!

You're welcome!