Count unique values in one column if values in corresponding columnare null?
 

I have 2 columns of values (numbers that contain text values). I need
to count the unique numbers in the one column if the column next to it
contains a null value.

I am using this formula to count the unique values (array formula) but
I only want it to calculate if the value in the corresponding cell is
null.
Please help.

=SUM(IF(FREQUENCY(IF(LEN(D2:D3130)0,MATCH(D2:D313 0,D2:D3130,0),""), IF
(LEN(D2:D3130)0,MATCH(D2:D3130,D2:D3130,0),""))0 ,1))

Count unique values in one column if values in corresponding colum
 

Hi,

Try this which will only handle numbers in column D and count them if column
C is empty.

=SUM(1*(FREQUENCY(IF((C1:C3130=""),D1:D3130),D1:D3 130)0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just enter. If you do it correctly then Excel will put curly brackets around
the formula{}. You can't type these yourself. If you Edit the ranges
then you must re-enter as An array

Or this which will count both numbers and text in column D if column c is
empty

=SUMPRODUCT((C1:C3130="")/COUNTIF(D1:D3130,D1:D3130&"")*(D1:D31300))

Mike

"allie357" wrote:

I have 2 columns of values (numbers that contain text values). I need
to count the unique numbers in the one column if the column next to it
contains a null value.

I am using this formula to count the unique values (array formula) but
I only want it to calculate if the value in the corresponding cell is
null.
Please help.

=SUM(IF(FREQUENCY(IF(LEN(D2:D3130)0,MATCH(D2:D313 0,D2:D3130,0),""), IF
(LEN(D2:D3130)0,MATCH(D2:D3130,D2:D3130,0),""))0 ,1))