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

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.