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

A

allie357

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:D3130,D2:D3130,0),""), IF
(LEN(D2:D3130)>0,MATCH(D2:D3130,D2:D3130,0),""))>0,1))
 
M

Mike H

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:D3130)>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:D3130>0))

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top