1

I am using the following formula to count the unique values in a range:

=SUMPRODUCT(('Sheet 1'!$E$5:$E$900<>"")/COUNTIF('Sheet 1'!$E$5:$E$900,'Sheet 1'!$E$5:$E$900&""))

This formula is on Sheet 2 and works fine. But now I am trying to add a condition to this which I cannot get to work. Instead of counting all values in Column E, I want to include only the values which have a value in Column M on the same row. If Column M is blank, I do not want to count what is in Column E. Can someone please tell me how I would do that?

Jack106
  • 11
  • 2

1 Answers1

1

With any luck you will have Excel-2007 or higher and can use this array formula.

=SUMPRODUCT(IFERROR((Sheet3!$E$5:$E$900<>"")*(Sheet3!$M$5:$M$900<>"")/COUNTIFS(Sheet3!$E$5:$E$900,Sheet3!$E$5:$E$900&"",Sheet3!$M$5:$M$900,"<>"),0))

An array formula require Ctrl+Shift+Enter to finalize rather than simply Enter.

  • Thanks Jeeped! I am using Excel 2013 and I added this formula but maybe I am doing something wrong. When I used the formula I mentioned I get 616 unique values. When I try your formula adding the condition I get 750. I should be getting a lesser value since not all rows in Column M have a value. – Jack106 Nov 05 '14 at 13:30
  • 1
    @Jack106 - please see my modification to an *array formula* above. –  Nov 05 '14 at 15:20